Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
GB GB is offline
external usenet poster
 
Posts: 230
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,939
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
GB GB is offline
external usenet poster
 
Posts: 230
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif & Sumif with Multiple criteria Kim Shelton at PDC Excel Worksheet Functions 6 September 25th 06 03:36 PM
Sum column information based on multiple criteria GHawkins Excel Worksheet Functions 8 August 24th 06 01:57 PM
Get cell value with multiple criteria lookup James Hobart Excel Worksheet Functions 1 October 28th 05 07:51 PM
Counting by multiple criteria Risky Dave Excel Worksheet Functions 4 September 28th 05 01:29 PM
Dynamic range names, multiple criteria, sumproduct [email protected] Excel Discussion (Misc queries) 1 September 20th 05 02:58 AM


All times are GMT +1. The time now is 09:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"