Multiple Criteria Lookup
 

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

Multiple Criteria Lookup
 

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...
--
HTH...

Jim Thomlinson


"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

Multiple Criteria Lookup
 

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

GB

"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"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

Multiple Criteria Lookup
 

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

=INDEX(Sheet1!D$2:D$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)

"GB" wrote in message
...
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