Formula not working - vlookup

Discussion in 'Microsoft Excel Discussion' started by John, Dec 1, 2011.

  1. John

    John Guest

    Hi. I have recently got Excel 2007 at work, and at home I have gone
    from 2000 to 2010.

    I have a vlookup formula as follows:

    =VLOOKUP(E2,Sheet2!E:O,3,0)

    It is comparing what is in cell E2 to find a match on sheet 2 in cell
    E. If a match is found it is returning the value in cell G.

    Unfortunately even though the value is there in cell E2 on sheet 1 and
    also sheet 2, it is not returning a positive value and instead I am
    getting #N/A! errors. Do you know what the issue could be?

    I can't see anything wrong with the formula or data. I have double
    checked the data to make sure it is the same format, and that is all
    okay with no difference. I was wondering if it could be something else
    that is preventing it with this newer version of excel that I need to
    enable or disable?

    Thanks,
    John
     
    John, Dec 1, 2011
    #1
    1. Advertisements

  2. John

    Pete_UK Guest

    Did it work in Excel 2000 with the same data? Does E2 contain a number
    or a text value (like name)? If it is a text value then you might have
    extra spaces (which are difficult to see !!), so you can use a formula
    like =LEN(E2) in a helper cell to see exactly how many characters
    there are. Use the same formula on the cell in Sheet2 that you think
    matches. You can get round this using something like:

    =VLOOKUP(TRIM(E2),Sheet2!E:O,3,0)

    if you have extra spaces in E2, or this:

    =VLOOKUP("*"&E2&"*",Sheet2!E:O,3,0)

    if you have extra spaces at the beginning or end in your lookup table
    (not extra spaces within the text).

    If you are using numbers then it might be that one or other is a text
    value that just looks like a number (formatting doesn't change that).
    In this case you could modify your formula to:

    =IFERROR(VLOOKUP(E2*1,Sheet2!E:O,3,0),IFERROR(VLOOKUP(E2&"",Sheet2!E:O,
    3,0),"not found"))

    Hope this helps.

    Pete

    On Dec 1, 12:52 am, John <> wrote:
    > Hi. I have recently got Excel 2007 at work, and at home I have gone
    > from 2000 to 2010.
    >
    > I have a vlookup formula as follows:
    >
    > =VLOOKUP(E2,Sheet2!E:O,3,0)
    >
    > It is comparing what is in cell E2 to find a match on sheet 2 in cell
    > E. If a match is found it is returning the value in cell G.
    >
    > Unfortunately even though the value is there in cell E2 on sheet 1 and
    > also sheet 2, it is not returning a positive value and instead I am
    > getting #N/A! errors. Do you know what the issue could be?
    >
    > I can't see anything wrong with the formula or data. I have double
    > checked the data to make sure it is the same format, and that is all
    > okay with no difference. I was wondering if it could be something else
    > that is preventing it with this newer version of excel that I need to
    > enable or disable?
    >
    > Thanks,
    > John
     
    Pete_UK, Dec 1, 2011
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page