Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel: VLOOKUP not returning expected results for repeated lookup values

Posted on 2004-10-25
7
Medium Priority
?
525 Views
Last Modified: 2010-05-18
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

0
Comment
Question by:jesselavaca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 6

Expert Comment

by:KingDumbNo
ID: 12405342
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
 
LVL 1

Author Comment

by:jesselavaca
ID: 12405907
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
 
LVL 3

Expert Comment

by:jng1
ID: 12408678
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 6

Accepted Solution

by:
KingDumbNo earned 1600 total points
ID: 12413590
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
 
LVL 1

Author Comment

by:jesselavaca
ID: 12425660
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
 
LVL 2

Assisted Solution

by:Fenric
Fenric earned 400 total points
ID: 12459226
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
 
LVL 1

Author Comment

by:jesselavaca
ID: 12585482
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question