Having trouble with vlookup

Hi All,

Vlookup is a function I use A LOT. I am very familiar with it, but today is the second occasion where I am having some trouble - so I'm hoping someone can help me out.

I have a list of site numbers that I need to pull information from another larger sheet.

All of the site numbers on sheet 1 are definitely on sheet 2, I have checked this.

They are both formatted in number style - nothing is formatted as text.

The formula I used is copied to make sure I am giving you exactly what's there:=VLOOKUP(A2,Sheet2!A:J,2,FALSE)

Column A on sheet 2 is where the site numbers are listed.

But I keep getting #N/A and when I do "Show calculation steps" the A2 value is italicized, so I think that is meaning it can't find the value on Sheet 2.

I can't think of anything else. Anyone have a clue?

I can post the spreadsheet if it is really necessary, but it has confidential information I'd have to remove so I'd rather not if we can help it.

Thanks in advance.

THe numbers are formatted EXACTLY the same. It's always the first thing I check in cases like these, because I'll get one column that's text a lot and so that has to get changed.

Originally Posted by Kim_J

All of the site numbers on sheet 1 are definitely on sheet 2, I have checked this

Hello Kim_J,

How did you determine that?

Assuming A2 looks like it matches sheet2!A10 for instance, what do you get with these 2 formulas?

=ISNUMBER(A2)

=ISNUMBER(sheet2!A10)

If the first gives TRUE and the second FALSE then try

=VLOOKUP(A2&"",Sheet2!A:J,2,FALSE)

If it's the reverse of that

=VLOOKUP(A2+0,Sheet2!A:J,2,FALSE)

If they both return the same then check that you don't have any extra characters in either cell, e.g. trailing spaces

I determined by physically looking - I did the ISNUMBER but both results were false because my site number has a letter in it (107J). I changed both of the site numbers on each sheet to 1071 just to test and then both results were true - and then my vlookup worked. But when I switched them both back to 107J it didn't work again??

Manage Attachments
Upload Errors
Copy of M3.zip:
Your file of 161.5 KB bytes exceeds the forum's limit of 100.0 KB for this filetype.




What am I doing wrong here?