Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default Retrieve data based on 3 criteria

In col A, starting at A3 have a list of customer names.
The columns B1:E1 are labeled 1,2,3,4 respectively, indicating option
numbers.
In Columns B2:E2 is the Product ID (which are identical in this group
of four). There are over 100 product ID's, each with options
available of 1,2,3 or 4.

Need to specify on another worksheet the customer located in col. A,
the option desired from row 1, (1,2,3 or 4) and the product ID from
row 2. Need to retrieve the value located within the table.
So, if a certain customer choses the product ID, with an option of 3,
it would retrieve the proper amount.

Tried retrieving with the numerical data with SUMIFS, but it did not
work. Perhaps a varient of SUMPRODUCT?
Thanks for pointing me in the right direction.
Pete

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 36
Default Retrieve data based on 3 criteria

On Mar 18, 1:21*pm, Pete wrote:
In col A, starting at A3 have a list of customer names.
The columns B1:E1 are labeled 1,2,3,4 respectively, indicating option
numbers.
In Columns B2:E2 is the Product ID (which are identical in this group
of four). *There are over 100 product ID's, each with options
available of 1,2,3 or 4.

Need to specify on another worksheet the customer located in col. A,
the option desired from row 1, (1,2,3 or 4) and the product ID from
row 2. *Need to retrieve the value located within the table.
So, if a certain customer choses the product ID, with an option of 3,
it would retrieve the proper amount.

Tried retrieving with the numerical data with SUMIFS, but it did not
work. *Perhaps a varient of SUMPRODUCT?
Thanks for pointing me in the right direction.
Pete


Easier if I see the actual file with before/after. send to
. Notice the 1
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 153
Default Retrieve data based on 3 criteria

On Mar 18, 11:21*am, Pete wrote:
In col A, starting at A3 have a list of customer names.
The columns B1:E1 are labeled 1,2,3,4 respectively, indicating option
numbers.
In Columns B2:E2 is the Product ID (which are identical in this group
of four). *There are over 100 product ID's, each with options
available of 1,2,3 or 4.

Need to specify on another worksheet the customer located in col. A,
the option desired from row 1, (1,2,3 or 4) and the product ID from
row 2. *Need to retrieve the value located within the table.
So, if a certain customer choses the product ID, with an option of 3,
it would retrieve the proper amount.

Tried retrieving with the numerical data with SUMIFS, but it did not
work. *Perhaps a varient of SUMPRODUCT?
Thanks for pointing me in the right direction.
Pete


I put the original data in Sheet1, with additional products in four-
column blocks F:I, J:M, etc.

In Sheet2, I used these parameters:
name in A1,
option in A2,
product ID in A3.

In Sheet2!A4, this formula seems to get the desired result:
=OFFSET(Sheet1!A1,
MATCH(A1,Sheet1!A:A,0)-1,
MATCH(A3,Sheet1!2:2,0)+A2-2)
[Since Sheet1!B1:M1 is so regular, there's no need to MATCH the
"option," just count columns.]

Hope this helps getting started.

You might want to add tests in case there isn't a match, or in case
you want an empty cell in Sheet1 to not return a zero in Sheet2!A4.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default Retrieve data based on 3 criteria

On Mar 18, 5:49*pm, zvkmpw wrote:
Looks great, and works nicely. Thank you.
Pete

I put the original data in Sheet1, with additional products in four-
column blocks F:I, J:M, etc.

In Sheet2, I used these parameters:
* name in A1,
* option in A2,
* product ID in A3.

In Sheet2!A4, this formula seems to get the desired result:
* *=OFFSET(Sheet1!A1,
* * * *MATCH(A1,Sheet1!A:A,0)-1,
* * * *MATCH(A3,Sheet1!2:2,0)+A2-2)
[Since Sheet1!B1:M1 is so regular, there's no need to MATCH the
"option," just count columns.]

Hope this helps getting started.

You might want to add tests in case there isn't a match, or in case
you want an empty cell in Sheet1 to not return a zero in Sheet2!A4.- Hide quoted text -

- Show quoted text -


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
How do I retrieve data based on multiple search criteria? ezaz123 Excel Worksheet Functions 1 January 20th 09 06:49 PM
How do I retrieve data based on multiple search criteria? ezaz123 Excel Worksheet Functions 0 January 20th 09 05:56 AM
Retrieve data when multiple criteria need to be met [email protected] Excel Discussion (Misc queries) 6 June 13th 08 06:03 PM
How to retrieve a value based on 2 different criteria littlejess22 Excel Worksheet Functions 2 August 23rd 07 09:34 AM
QUICK HELP - Retrieve value based on two column criteria jay Excel Discussion (Misc queries) 1 September 23rd 05 01:20 AM


All times are GMT +1. The time now is 01:44 PM.

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

About Us

"It's about Microsoft Excel"