Solved

Excel: VLOOKUP not returning expected results for repeated lookup values

Posted on 2004-10-25
491 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
Question by:jesselavaca
    7 Comments
     
    LVL 6

    Expert Comment

    by: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
    0
     
    LVL 1

    Author Comment

    by:jesselavaca
    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
    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
     
    LVL 6

    Accepted Solution

    by:
    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
    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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    The first thing most Office 2007 users will notice about Office 2010 is that the File menu is back – great, no more “office blob”. All the Office applications (including Visio and Project) now share the same updated ribbon, so the hotch-potch that w…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    846 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now