Formula not working - vlookup

[John]

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

 

[Ron Rosenfeld]

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

Most likely, the problem is with your data. What looks the same is not always the same to Excel.

What sheet is E2 on? I will assume it is on Sheet1

For a value in E2 that fails, please provide the following:

1. Select Sheet1!E2, then copy/paste the contents of the formula bar:


2. Select the matching cell on Sheet2!E? and copy/paste the contents of the formula bar:


3. Enter the following formulas in some cell and copy/paste the results here:

=ISTEXT(Sheet1!E2) (or whatever sheet your lookup_value in E2 is located)

=ISTEXT(Sheet2!E?) where ? is the row of the cell in column E that you think matches Sheet1!E2

 

[Ron Rosenfeld]

What sheet is E2 on? I will assume it is on Sheet1

I see you already answered that in your initial question. Please ignore.

 

[Pete_UK]

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