lookup

[Guest]

Is there a way to lookup information in a table using both colm A and B as
the lookup value?

For example I have a list of names in Colm A and in Colm B I have one of the
four (1) Premium (2) Comm (3) Paid (4) Profit. The four things in colm B are
listed over and over again for each name in colm. Colm's C - ?? are months
Jan - Dec.

I need to be able to write a formula that will find Cindy in colm A and
Premium in colm B and then return the x colm #. Just like vlookup only with
two lookup values.

Hope this makes sense. Any help will be greatly appreciated.

 

[vezerid]

F1 has the name, F2 has the classification (Premium etc). The
following *array* formula will do:

=INDEX(C2:C100,MATCH(F1&F2,A2:A100&B2:B100,0))

Since it is an array formula, you must commit it with key combo Ctrl
+Shift+Enter

HTH
Kostis Vezerides

 

[Guest]

Vezerid. Thank you very much for your reply. This helps alot. One
additional question is you dont mind. Using the formula that you gave me, if
C2:C100 is information for January and D2100 is information for Febraury
and so on through December, is there a way to use a formual so that it will
know which month to look for?

Each row that the formula is on shows the month it is for so I could easily
use a month() to determin which colm in the index to use.

Thanks again for your help.

 

[vezerid]

You need to replace C2:C100 in the formula I gave you with one of
various expressions that can make the range dynamic. The solution I am
proposing assumes that you have the month numbers in row 1 (C1:N1) and
that F3 contains the month in question

=INDEX(OFFSET(B2:B100,MATCH(F3,C1:N1,0)),MATCH(F1&F2,A2:A100&B2:B100,0))

HTH
Kostis