[Answered] Match From Data Sheet "With Based On Drop Down

[Muh]

Answer Required Through Excel Formula in Sheet1 and Sheet2

Sheet 1"Summary Division" Colum B4 Drop Down Given
Sheet 2"Summary Branch" Colum B4 Drop Down Given

Sample Answer Give Yellow Higlighted

If Drop Down List Change Then Concerned Branch Or Division Sales And Profit Data Pick From Data Sheet as Prescribed Format

Data Sheet "Colum A" Mentioned Division Names
These are the Data Sheet
Sales 2021
Sales 2022
Sales 2023
profit 2021
profit 2022
profit 2023

 

[shamimarita]

Hello Muh,

You can use the XLOOKUP function to import data from sales and profit sheets.

Sales:
=XLOOKUP(B4,'Sales 2021'!B3:B25,'Sales 2021'!C3:Q25,1)


Profit:
=XLOOKUP(B4,'profit 2021'!B3:B26,'profit 2021'!C3:Q26,1)



Excel File:

 

[Muh]

1.Mentioned Formula is Not Associated Branches 2nd Screen Short. If may Wrong Data If Summary Sheet Branch Name Change

2. Data Sheet Colum-A Division name Duplication. If sum Value Required



Please Can get a much Formula
.

 

[shamimarita]

To accommodate branch-specific matching and summation for duplicate division names, you can use the following formula in Sheet1 or Sheet2:

Formula for Sales/Profit:

=SUMIFS('Data Sheet'!C:C, 'Data Sheet'!A:A, $B$4, 'Data Sheet'!B:B, A5)

Explanation:

1. $B$4 refers to the branch dropdown in Sheet1/2.
2. A5 refers to the division name in the respective row.
3. SUMIFS handles summation for duplicate entries in the Data Sheet.

Apply a similar formula for profits by adjusting the relevant column range.

 

[Muh]

Formula Not Working properly . If use Sumifs Formula then Data Sheet Sum range individually Select in Formula!!!.

 

[shamimarita]

You can use the provided idea to create a solution tailored to your requirements. Instead of selecting ranges individually, consider structuring your data for seamless formula application. For example, ensure consistent headers and column alignment in the Data Sheet. Using structured references or tables can also streamline the process.

If the SUMIFS approach doesn't fully meet your needs, you could explore Pivot Tables or helper columns to summarize data dynamically.