Referencing 3 values for a lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chad - 16 Aug 2006 15:10 GMT

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

Reply to this Message

David Billigmeier - 16 Aug 2006 15:21 GMT

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="Value"))

Change the ranges to fit your data.

Signature

Regards,
Dave

> 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
[quoted text clipped - 14 lines]
> Thanks in advance for your help,
> Chad

Reply to this Message

Dav - 16 Aug 2006 15:31 GMT

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

Signature

Dav

Reply to this Message