A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Vlookup and Data Validation Q



 
 
Thread Tools Display Modes
  #1  
Old April 18th, 2010, 02:29 PM posted to microsoft.public.excel.worksheet.functions
Seanie
external usenet poster
 
Posts: 71
Default Vlookup and Data Validation Q

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?
  #2  
Old April 18th, 2010, 03:54 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default Vlookup and Data Validation Q

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" wrote in message
...
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?



  #3  
Old April 18th, 2010, 04:33 PM posted to microsoft.public.excel.worksheet.functions
Sheeloo
external usenet poster
 
Posts: 797
Default Vlookup and Data Validation Q

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?
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.