?
Solved

vlookup return N/A

Posted on 2011-05-10
12
Medium Priority
?
375 Views
Last Modified: 2012-05-11
I have a table that hundreds of part numbers,  I am doing a Vlookup to another sheet that has the number and English descriptions and I want to return the english description.  when I do this VLOOKUP(A2,XData!$A:$B,2,FALSE)

I get #N/A.
0
Comment
Question by:tips54
12 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35732588
You will need to post a sample sheet and an example of a case where the formula fails so that we can help you...
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35732591
can u check if there are additional spaces or NBSP in the columns? it would help if you can post the spreadsheet. its hard to guess...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35732603
this is usally because either:
* XData is not sorted on A column
* try TRUE instead of FALSE
* the "formatting" of the Xdata:A and A2 are not the same (one is in "number" format, the other in "text" format

hope this helps
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:tips54
ID: 35732730
I checked the format and they seemed correct. this file attached.
X.xls
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35732733
Supposing the matching value in column A of Xdata is in A20 then type this formula in the existing sheet

=A2=Xdata!A20

If it is false then this is the reason for the #NA. You must make the two match.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 375 total points
ID: 35732742
Try

=VLOOKUP(--A2,xData!$A:$B,2,FALSE)
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35732756
Actually sheet mob has a text value whereas sheet Xdata has a numerical value.

The above formula will convert the text to a number before the search.

Saqib
0
 

Author Comment

by:tips54
ID: 35732881
Saqib,

That seems to work some. I'm still getting some NA and i'm getting #VALUE!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35732948
Check it out with my first comment. If you can show us how it is different then we might be able to work out a formula which works in all cases
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35732953
Or just upload some of such cases and we can try to find a way out
0
 
LVL 8

Expert Comment

by:wchh
ID: 35735189
mod!A1 = text
xData!$A = numeric
Thus, you need to convert Text to numeric and search...

Try formula below:
=VLOOKUP(IF(ISTEXT(A2),VALUE(A2),A2),xData!$A:$B,2,FALSE)
0
 

Author Closing Comment

by:tips54
ID: 35740839
thank you
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question