VLOOKUP turning up #N/A even when matched

July 26, 2011 at 12:16:02
Specs: Windows 7
I have configured VLOOKUP for a spreadsheet as follows:
=VLOOKUP($A5,$I$2:$J$6657,1,FALSE)

It is meant to match on name, and it works fine when manually entering each name. However, I require this to work for over 1700 records, and when I copy and paste the values into column A, no matches turn up (which is wrong). Any ideas of what could be causing this?

Thank you everyone!


See More: VLOOKUP turning up #N/A even when matched

Report •


#1
July 26, 2011 at 12:21:04
When you look at the formula after the paste, has the formula changed? are all the cell reference still valid?

I think it just hit me. In your pasted data, are you sure there are no spaces at the end? And that your characters from the paste are the same as the characters in the column you are searching for? usually spaces at the end are the killer because you cant see them when just looking at it.

(one example of characters that appear the same but may be viewed as different)

I am not sure if a space and a non-breaking space are viewed as the same when doing a vlookup.


Report •

#2
July 26, 2011 at 12:38:00
Thank you very much Kitty Canine, it was caused because of the spaces.

Follow-up question. It looks like when you select a cell (or group of cells) in excel and copy them, it automatically populates the extra space when pasting it. Any idea how to avoid this? Thanks!


Report •

#3
July 26, 2011 at 12:55:27
You probably have spaces in the original data. You can test this by editing one of your source cell and using the mouse, place the cursor a before the last letter, number or symbol. Then press the right arrow once. your cursor should now look like its at the end. if you press it again you should see it move again to the right. The number of times it moves is the number of spaces.

Alternatively try this assuming your source data is in column A starting row 5

=SUBSTITUTE(A5," ","+")

this should convert all spaces to + so you can actually see them.

There are ways of stripping out the extra spaces at the end. First do a check and see if there is a pattern, always just one space? if the number of spaces at the end varies, can you tell what the maximum number of spaces is?

if its a constant number just place the following in column B

=left(A5,len(A5)-NUMBER OF CONSTANT SPACES)

if it varies let me know and there are a few ways of dealing with it. The easiest is through VBA but that is not my forté. I deal with it through a series of if statements but I can only deal with a certain maximum number of spaces at the end.


Report •

Related Solutions

#4
July 26, 2011 at 14:22:44
There may not be a need todetermine how many spaces there are.

Try the TRIM and CLEAN functions.

=TRIM(A1)

Removes leading and trailing spaces,

=CLEAN(A1)

Removes all nonprintable characters from text.

=TRIM(CLEAN(A1))
=CLEAN(TRIM(A1))

Rarley, but sometimes, needed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
July 26, 2011 at 14:25:34
re: "It looks like when you select a cell (or group of cells) in excel and copy them, it automatically populates the extra space when pasting it. Any idea how to avoid this?"

This is definitely not caused by Excel's copy/paste functions.

I don't even want to think of the implications if that was the case.

As Kitty said, the issue is most likely with your original data, which can often be the case when importing data for text files, websites, etc.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
July 26, 2011 at 14:45:00
I was unaware of the Trim function...

Much more elegant than my solution!

Wish I had known that one a couple of years ago.

So based on DerbyDad03's suggest formula, try the following:

=VLOOKUP(Trim($A5),$I$2:$J$6657,1,FALSE)


Report •

Ask Question