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?
 
KingDumbNoConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
FenricConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.