Vlookup and Data Validation Q

S

Seanie

I am trying to use a DataValidation cell value to do a lookup and I
get #N/a

Cell N101 = a Data Validation list value
Lookup formula in O101 = =VLOOKUP(N101,Sheet3!A$3:C$248,1,FALSE)

Above returns #N/a, but the value I want is in Sheet3!A3 and N101 is
in Sheet3!B3

What am I doing wrong?
 
M

Max

Vlookup doesn't work that way

Use index/match to easily return results from a col to the left or right of
the match col
In O101: =INDEX(Sheet3!A:A,MATCH(N101,Sheet3!B:B,0))
 
S

Sheeloo

Value in N101 should match one of the values in the column A... the value you
want returned as the result of the formula can be in the adjacent cell in Col
B or Col C... In other words the value to be matched has to be in the first
col in the range given as the second parameter in VLOOKUP.

If it is in B then use
=VLOOKUP(N101,Sheet3!A$3:C$248,2,FALSE)
or
=VLOOKUP(N101,Sheet3!A$3:B$248,1,FALSE)

If it is in C then use
=VLOOKUP(N101,Sheet3!A$3:C$248,3,FALSE)

Notice the change from 2 to 3... it indicates the number of col you want the
value to be returned.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top