Solved

vlookup return N/A

Posted on 2011-05-10
Medium Priority
375 Views
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
Question by:tips54

LVL 33

Expert Comment

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

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

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

Author Comment

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

LVL 43

Expert Comment

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

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

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

LVL 43

Expert Comment

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

ID: 35732881
Saqib,

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

LVL 43

Expert Comment

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

ID: 35732953
Or just upload some of such cases and we can try to find a way out
0

LVL 8

Expert Comment

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

ID: 35740839
thank you
0

Featured Post

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ā¦
Suggested Courses
Course of the Month17 days, 6 hours left to enroll

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.