Multiple Criteria Lookup

[Guest]

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

 

[Guest]

How about a pivot table? Place your cursor in the middle of your source data
and select Data -> Pivot Table. Follow the wizard. Move your data fields
around until it looks just the way you want. If you want seperate tabs for
each division, then put the division in the filter at the top of the table
and then right click and select Show Pages...

 

[Guest]

Jim, thanks, but I was looking for more of a INDEX/MATCH formula to use.

GB

 

[Bob Phillips]

Assuming that the category values are in B1:B3 on sheet2, in C1, enter

=INDEX(Sheet1!D$2$10,MATCH(1,(Sheet1!$B$2:$B$10="Division
1")*(Sheet1!$C$2:$C$10=$B1),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

and copy down and across

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)