• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 565
  • Last Modified:

MsOffice Excel 2010 vLookup seems not to work properly

Attached is an excel with the function of vLookup seems not to work.  Note that the table-array contents and the lookup-value are the same, there values that are the same but the result displays #N/A.

Why is this happening and how can we fix it.
3 Solutions
rayluvsAuthor Commented:

Some Numbers are stored as Text pls convert

VLOOKUP has problem with it some times

rayluvsAuthor Commented:
Don't understand.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

barry houdiniCommented:
You can make a small adjustment to the formula to convert the lookup value to text (because all values in column A are also text), i.e. this version


regards, barry
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

you may want to have a look at this article by matthewspatrick: Six reasons why your Vlookup formula does not work

The value in D2 (and D3 and D6) is a number. It was entered as a number. It looks as if the format of the cells has been changed to "Text", but this alone will not transform a number into text. If you select the cell, hit F2 to edit it, then hit Enter immediately, THEN the cell will be text.

To see if a number is stored as text, you should turn on the warning indicators with File > Options > Formulas > Error checking rules > Numbers formatted as text or preceded by an apostrophe

You will see that cells D2, D3 and D6 don't show that warning triangle.

If you don't want to change the data in these cells, you could adjust the Vlookup formula, starting in cell E2


copy down.

cheers, teylyn
rayluvsAuthor Commented:
Worked excellently!!!

Please explain how the &"" solved this problem (we would like to understand)
rayluvsAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now