VLOOKUP() Returns #N/A, No Trailing Spaces, All Cells General Format

Thank you for looking at my question,

I have a workbook in which I am trying to run VLOOKUP() function. The search string on sheet 1 and the target on sheet 2 are of the same length (no leading/trailing spaces) and the cells in each sheet are formatted as General.

The search string is present in the target data.

There is a difference between the two datasets in the way the all-numeric cells are displayed; on sheet 1 cells containing all-numeric strings display their contents as if it were a number (right-justified) but on sheet 2 the same string is displayed as a string (left-justified) and with a comment about the number being stored as text but this is the only difference.

How do I overcome this issue please?


I have attached the file in question.
PreProjectAnalysis.xlsx
Gary CroxfordOperations Support AnalystAsked:
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.

Rgonzo1971Commented:
Hi,

Since your Inventory data is text then try

=VLOOKUP(TEXT(B3;"@"),Inventory!A1:D6548;2,0)

Open in new window

Regards
0
SteveCommented:
you need to use either:

=VLOOKUP(""&B3,Inventory!$A$1:$D$6548;2,0)
if looking a number up to text

or
=VLOOKUP(--B3,Inventory!$A$1:$D$6548;2,0)
if looking text up to a number
0
Gary CroxfordOperations Support AnalystAuthor Commented:
Ronzo1971:

My Excel (2013 if that's significant) doesn't like this formula - gives the 2We Found A Problem ..." message and when I click OK the cell value B3 is highlighted in the formula text in the formula bar - any idea why this may be?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Gary CroxfordOperations Support AnalystAuthor Commented:
Steve:

My Excel (2013 if that's significant) doesn't like this formula - gives the 2We Found A Problem ..." message and when I click OK the cell value $D$6548 is highlighted in the formula text in the formula bar - any idea why this may be?
0
Rgonzo1971Commented:
Sorry forgot two ;

=VLOOKUP(TEXT(B3,"@"),Inventory!A1:D6548,2,0)
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
Gary CroxfordOperations Support AnalystAuthor Commented:
worked perfectly, thank you
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.

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.