Lookup and Display Using Multiple Criteria
 

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

Lookup and Display Using Multiple Criteria
 

GB wrote:
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

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

Lookup and Display Using Multiple Criteria
 

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

Lookup and Display Using Multiple Criteria
 

Ken, it works great. Thanks a bunch.

GB

"Ken Johnson" wrote:


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

Lookup and Display Using Multiple Criteria
 

You're welcome GB.
Thanks for the feedback.

Ken Johnson