lookup
 

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.

lookup
 

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

On Feb 14, 5:57 pm, Jordan wrote:
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.

lookup
 

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 D2:D100 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" wrote:

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

On Feb 14, 5:57 pm, Jordan wrote:
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.

lookup
 

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

On Feb 14, 8:24 pm, Jordan wrote:
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 D2:D100 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" wrote:
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

On Feb 14, 5:57 pm, Jordan wrote:
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.