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

 

[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