Vlookup Problem

[Mike]

Good afternoon everyone,
Using Windows & Excel XP

I'm having a problem with the =VLOOKUP function. In the help section it
says that it searches for a value in the leftmost column of a table. What I
want it to is to search the first two column of a table. For example I have
a worksheet called "winners" that looks like this,

A B C
-------------------------------
1 0 0 Nothing
2 0 1 $3
3 1 1 $4
4 2 0 Nothing
5 2 1 $7


on a previous page i have data as follows:

A B
----------------------
1 2 0
2 1 1
3 0 1

How would I write the vlookup formula to check the data in A1 & B1 (2 & 0)
and return a value of "nothing" in C1?

Thanks,
Mike

 

[Bob Phillips]

=INDEX(Sheet2!C1:C10,MATCH(A1&B1,Sheet2!A1:A10&B1:B10,0))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dr. Vladimir Mindin]

Primitive but working solution is to insert D column and concatenate values
from column A and B
It will produce table like this
A B C D
------------------------------------
1 0 0 00 Nothing
2 0 1 01 $3
3 1 1 11 $4
4 2 0 20 Nothing
5 2 1 21 $7
now you are able to use range $C$1:$D$5 in VLOOKUP function.
Good idea is to name this range. I've named it TEST
Now on the previous page in the C1 you place VLOOKUP function
=VLOOKUP(A1&B1,TEST,2,FALSE) and get "Nothing"
After you populate this formula to C2 and C3 you get 4 and 3
Best regards,
Vladimir