Retrieve data based on 3 criteria

[Pete]

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

 

[Donald Guillett]

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
(e-mail address removed). Notice the 1

 

[zvkmpw]

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.

 

[Pete]

Looks great, and works nicely. Thank you.
Pete