Excel: VLOOKUP not returning expected results for repeated lookup values

Hello experts,

I'm working on an Excel spreadsheet that needs to perform a lookup.  

Table A is looking up a value in Table B and returning the corresponding value in Table B to Table A. Table B contains only unique lookup values, while Table A contains multiple occurances of the same lookup value. The first instance of the value in Table A returns the correct result while all duplicates return error messages (#N/A).

Here's the Vlookup formula: =VLOOKUP($A1,'Master'!$A:$H,6,FALSE)

Any thoughts?  Let me know if you need more info...

-Jess

LVL 1
jesselavacaAsked:
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.

KingDumbNoCommented:
Do any rows in column F on the Master worksheet have the #N/A error?  I've tried a hundred different things to try and duplicate, but cannot.  Can you post the workbook somewhere for a direct look?

Regards,
Emory
0
jesselavacaAuthor Commented:
No rows on the master worksheet have the #N/A error.

Could it have something to do with the fact that the value being used to do the lookup is actually a concatenation of a few different columns?

My company wouldn't be happy if I posted this info somewhere, is there any other way I can describe it to you?

Thanks for the help.

Jess
0
jng1Commented:
Not  sure which version of excel you are using.

Is Master a seperate worksheet (another tab on bottom)
or a seperate Workbook (different excel file)?

This is what the my lookup looks like using Excel 2002 (aka Excel XP):
same excel file (tab on bottom):
=VLOOKUP($A1,Sheet2!A1:H16,6,FALSE)
   ** Note no single quote **

two different files:
=VLOOKUP($A1,[File1.xls]Sheet1!$A$1:$H$16,6,FALSE)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

KingDumbNoCommented:
Jess,

Sorry I'm just getting back...

Regarding concatenation: Wrap your lookup value within the TRIM function.
=VLOOKUP($A1,'Master'!$A:$H,6,FALSE)
becomes
=VLOOKUP(TRIM($A1),'Master'!$A:$H,6,FALSE)

I would also like to verify that the formula, after making a change is being copied down and not across.

I should respond quicker to your next comment.

Regards,
Emory
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
jesselavacaAuthor Commented:
Emory: Hmm, TRIM didn't change the outcome...yet.

Sorry for the delay, things have been really busy.  Friday I might try to recreate a file without confidential data and post it somewhere...be back Friday!

Thanks,
Jess
0
FenricCommented:
Hi, Jess.

The problem must be with your concatenate function. Your concatenation is occurring on sheet A? Is the concatenation a straight "concatenate" or are you assembling a cell value from other cells using VBA? Also, what format are the concatenated keys in... text, number, date, etc.?

Sounds to me like you might have some key fields appearing in "text string" format when your lookup is numerical, or vice versa. If the original entry of each key correctly returns a VLOOKUP value, but subsequent ones do not, there must be a difference between them, even if they appear the same.

Alternatively, are you constructing your VLOOKUP formula using VBA? If the formula changed from cell to cell that might explain this also.

Intriguing problem.

Regards
Fenric!
0
jesselavacaAuthor Commented:
Sorry for the delay, experts.

I've fixed the problem with your help - it was the pesky extra spaces fixed using TRIM() - but I had to trim the lookup values in both tables, and then it fixed the problem.  Thanks, and sorry for taking so long to get back to you...

-Jess
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 Office

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.