asked on

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

KingDumbNo

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

jesselavaca

ASKER

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

jng1

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)

ASKER CERTIFIED SOLUTION

KingDumbNo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jesselavaca

ASKER

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

SOLUTION

Fenric

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jesselavaca

ASKER

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