VLOOKUP in Excel

[funkylad20]

I have a list of numbers (Unique Reference Numbers) and attached to each URN is an email address.

I have typed a VLOOKUP to pull through email addresses from one sheet to another. It pulls through some, but not others (even thought they are definitely there).

All it displays is #N/A.

Can anyone suggest why this is doing this? I assume it is something to do with formatting, but have tried copying data and paste specialed the 'values' to overcome this, still no joy.

[Vagrant]

vlookup gets confused if there leading spaces

[funkylad20]

Sorry?

[Vagrant]

If you've got spaces in front of the number you're trying to match on it gives this type of error.

[dundurn]

Vagrant is right. VLOOKUP is a great function, but it does depend on finding exactly what you're searching for. So leading or trailing spaces (in either sheet) will mess up your results. If you set the format of both columns to general you may well see values which are apparently numbers switch to the 'text' side of the column. If all of the references are definitely meant to be purely numerical (or at the very least should never contain a space), you can apply a find and replace for a single space and replace it by null in both sheets.

There is still another possibility. When you wrote the VLOOKUP did you refer to the search area by specific coordinates (e.g. B1:X300)? If so, when you dragged the formula down through the rows this would have changed to B2:X301, B3:X302 etc. Some of your searches would have worked but others wouldn't because they are no longer in the defined area. To avoid this make sure your initial search area is fixed (by using $B$1:$X$300, or $B:$X)