Prev: What does this formula mean [$-F400]h:mm:ssAM/PM
Next: how do I perform an interpolation analysis using Excel?
From: Seanie on 18 Apr 2010 09:29
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?
From: Max on 18 Apr 2010 10:54
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))

--
Max
Singapore

"Seanie" <seanryanie(a)yahoo.co.uk> wrote in message
news:7243d818-5359-4aa4-a2a1-eb176b6a6a80(a)b23g2000yqn.googlegroups.com...
>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?


From: Sheeloo on 18 Apr 2010 11:33
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.

"Seanie" wrote:

> 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?
> .
>
 | 
Pages: 1
Prev: What does this formula mean [$-F400]h:mm:ssAM/PM
Next: how do I perform an interpolation analysis using Excel?