Lookup and Display Using Multiple Criteria

G

GB

What's the best way to return a specified cell value from a separate
tab/sheet, using multiple criteria, and without sorting the source data/table
(see below example)?

Tab 1 / Sheet 1 - Input Data
B2:B10 Division Values - Division 1, Division 2, or Division 3
C2:C10 Category Values - Revenue, Expenses, Profit
D2:O10 Amounts - Monthly amounts for each B2:B10 Divisions and B2:B10
Categories

Tabs 2, 3 and 4 - Extracted Lookup and Display Data
In 3 separate sheets/tabs dedicated to each Division, I want to display, in
Cells C1 through N3, the monthly Amounts (i.e. the appropriate row of monthly
amounts) from Tab 1 based on the the Divisions entered in Cell A1 of Tabs 2-4
(e.g. Tab1, Cell A1 = Division 1), and the Categories entered in Cells B1:B3.

Thanks,

GB
 
K

Ken Johnson

GB said:
What's the best way to return a specified cell value from a separate
tab/sheet, using multiple criteria, and without sorting the source data/table
(see below example)?

Tab 1 / Sheet 1 - Input Data
B2:B10 Division Values - Division 1, Division 2, or Division 3
C2:C10 Category Values - Revenue, Expenses, Profit
D2:O10 Amounts - Monthly amounts for each B2:B10 Divisions and B2:B10
Categories

Tabs 2, 3 and 4 - Extracted Lookup and Display Data
In 3 separate sheets/tabs dedicated to each Division, I want to display, in
Cells C1 through N3, the monthly Amounts (i.e. the appropriate row of monthly
amounts) from Tab 1 based on the the Divisions entered in Cell A1 of Tabs 2-4
(e.g. Tab1, Cell A1 = Division 1), and the Categories entered in Cells B1:B3.

Thanks,

GB
Click to expand...

Hi GB,

Does this do what you are after?...

=SUMPRODUCT(('Input Data'!$B$2:$B$10=$A$1)*('Input
Data'!$C$2:$C$10=$B1)*('Input Data'!$D$2:$D$10))

in C1 on the three Division Sheets fill across for the 12 months and
down to row 3 for the three categories.

Ken Johnson
 
K

Ken Johnson

Oops!
That only gives the January figures. Need to get rid of the $'s in the
third array argument column addresses...

=SUMPRODUCT(('Input Data'!$B$2:$B$10=$A$1)*('Input
Data'!$C$2:$C$10=$B1)*('Input Data'!D$2:D$10))

Ken Johnson
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Criteria Lookup 3
moving data to multiple tabs 2
Using a cell's value to copy another cells info to another workshe 1
Multiple Criteria for worksheet 4
combine information from multiple file into many different files. 2
Vlookup help 2
Extracting data from database into separate sheets 0
> but < subtotal (3,...) or countif 3

Top