Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Forums » Excel Forums » Functions and Formulas » VLOOKUP issue

VLOOKUP issue

Previous | Next
Latest post Fri, Mar 14 2008 9:36 AM by Simon Herbert. 4 replies.

  • Wed, Feb 6 2008 5:53 PM

    • Mac
    • Top 500 Contributor
    • Joined on Wed, Feb 6 2008
    • Posts 1
    • Points 53

    VLOOKUP issue

    Reply |Contact

    Give me some insight to this problem

    I have a several cells that retreive data from a table via the VLookUP function; fine, no problem

    One of the above cells is use by another cell as the basis to lookup values in another table, which always returns #N/A.

    If I simply manually retype the cell value, the contingent cells correctly retreive data from the table.

    All above referenced cells and the tables are in separate worksheets within the same workbook. 

     

    Mc/ 

    Michael McCarty eXecutive Office Technologies Edmond, OK
    Filed under: ,
    • Post Points: 53

  • Wed, Feb 6 2008 6:01 PM In reply to

    • Roger Govier
    • Top 10 Contributor
    • Joined on Wed, Jan 9 2008
    • Abergavenny, South Wales, UK
    • Posts 74
    • Points 1,231
    • MVP

    Re: VLOOKUP issue

    Reply |Contact |Mark as Answer

    Can you post some sample data, including the formula that gives rise to the #N/A message, and the value you type in palce of the formula which then produces the correct result.

     

    Regards
    Roger Govier
    Microsoft Excel MVP
    • Post Points: 5

  • Wed, Feb 6 2008 6:17 PM In reply to

    • Jim Thomlinson
    • Top 10 Contributor
    • Joined on Thu, Jan 10 2008
    • Vancouver, Canada
    • Posts 84
    • Points 1,256

    Re: VLOOKUP issue

    Reply |Contact |Mark as Answer

    My default answer for this type of problem is normally either you are comparing a string to a number and therefore not getting a return value or you are comparing 2 strings where one of the strings is padded with blank characters at the end... 
    • Post Points: 5

  • Thu, Feb 7 2008 2:45 AM In reply to

    • dermot
    • Top 25 Contributor
    • Joined on Wed, Jan 9 2008
    • Perth
    • Posts 39
    • Points 940

    Re: VLOOKUP issue

    Reply |Contact |Mark as Not Answer

     Mac, it could be because the number returned by the first lookup is treated as text, whereas the number you are looking up in the second table is treated as a number.

    Try putting VALUE(  )  around the first lookup, to convert it to a number, before doing the second lookup,ie

    = VLOOKUP( VALUE( LOOKUP( ..... 

     
    • Post Points: 21

  • Fri, Mar 14 2008 9:36 AM In reply to

    Re: VLOOKUP issue

    Reply |Contact |Mark as Answer

    dermot:

     Mac, it could be because the number returned by the first lookup is treated as text, whereas the number you are looking up in the second table is treated as a number.

    Try putting VALUE(  )  around the first lookup, to convert it to a number, before doing the second lookup,ie

    = VLOOKUP( VALUE( LOOKUP( ..... 

    Or if they are the other way round (number in the first table, text in the second), use the TEXT() function (specify "@" as the format)

    = VLOOKUP( TEXT( LOOKUP( ..... ),"@"),......
    • Post Points: 5
Page 1 of 1 (5 items) | RSS
Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.