I have a spreadsheet that contains data that has three identifiers: Week,
Type & Coupon. I was wondering if there was a way to reference this data by
calling out these three identifiers.

Example:

200701 200701 200702 200702 200703 200703
Count Value Count Value Count Value
Coupon1 2 10.5 3 15.4 9 21.4
Coupon2 5 2.3 10 12.8 16 16.7
Coupon3 7 8.4 2 4.3 16 12.7

I would like to Reference which Coupon, Type and Week in order to get a value:

Coupon1 Value 200702 15.4


Thanks in advance for your help,
Chad

I have made a couple assuptions as to what cells your values occur. First,
your Coupon 1,2,3 values occur in A3,A4,A5... and your dates occur in
B1,C1,D1, etc.

Also:
The Coupon value you want to look up is located in A7.
The option where you choose "Count" or "Value" is located in A8
The Date value is located in A9.

All that being said, here is your formula:

=VLOOKUP(A7,$A$3:$G$5,MATCH(A9,$A$1:$G$1,0)+(A8="V alue"))

Change the ranges to fit your data.

--
Regards,
Dave


"Chad" wrote:

I have a spreadsheet that contains data that has three identifiers: Week,
Type & Coupon. I was wondering if there was a way to reference this data by
calling out these three identifiers.

Example:

200701 200701 200702 200702 200703 200703
Count Value Count Value Count Value
Coupon1 2 10.5 3 15.4 9 21.4
Coupon2 5 2.3 10 12.8 16 16.7
Coupon3 7 8.4 2 4.3 16 12.7

I would like to Reference which Coupon, Type and Week in order to get a value:

Coupon1 Value 200702 15.4


Thanks in advance for your help,
Chad

If your data is in a1:g5 so coupon1 is in a3

and the parameters you are wishing to match are coupon in a7, date in
a8, value in a9

try something like

=SUMPRODUCT((B1:G1=A8)*(B2:G2=A9)*OFFSET(B2,MATCH( A7,A3:A5,0),0,1,6))

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=572196