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.
rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
0
Rgonzo1971Commented:
Hi,

Some Numbers are stored as Text pls convert

VLOOKUP has problem with it some times

Regards
0
rayluvsAuthor Commented:
Don't understand.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

=VLOOKUP(D2&"",A$1:B$7,2,FALSE)

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

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

=VLOOKUP(TEXT(D2,"0"),A$1:B$7,2,FALSE)

copy down.

cheers, teylyn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
Worked excellently!!!

Please explain how the &"" solved this problem (we would like to understand)
0
rayluvsAuthor Commented:
thanx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.