VLOOKUP in Excel 2007
I have 2 columns on an excel spread sheet with the following formula; VLOOKUP(D2,$A$2:$B$2090,2,FALSE) if I copy from column one cell A(2thur209) it produces the corresponding name in column B, but if I type the same name it produces #N/A. What is the problem?
Software/Hardware used:
Software/Hardware used:
ASKED:
April 16, 2009 10:57 PM
UPDATED:
August 12, 2009 2:35 PM
Answer Wiki:
The VLOOKUP function returns #N/A when the searched value does not exist in the selected range of cells.
Could you please help us understand the scenario ?
If someone else has completly understood this, please overwrite the answer.
What information/value is in your D2 cell ?
From what cell and to what cell are you copying ? (example: from A6 to A50)
What are the contents of the source and target cells before copying ?
Thanks,
My guess (intuition only) is that you have some cells with spaces at the end of them. When you type, Excel truncates spaces, when you copy/paste a cell, it doesn't.
Try looking at your lookup table. If it turns out that is the case, you can save a lot of typing by using a formula temporarily like C2 = TRIM(A2). You then copy column C, and use paste special, values, on column A. This removed leading or trailing spaces in all cells in one go. You can then remove column C
Derek
To see all answers submitted to the Answer Wiki: View Answer History.
I have 2 columns, 1st column client names, 2nd column people who refered those clients. =VLOOKUP(C2,$A$2:$B$180,2,FALSE) C2 is where I enter one of the client names in order to retrive the individual who refered the client, and it works when I copy directly from the client column, but if I type in the client name it returns #N/A. I need some help!
Strange,
Have you checked if this happens with every name of the list, or just with some of them ?
I can only think of some ‘invisible’ character in the names in column A. I would try copying one of those names to some text editor (notepad for example) to see what it looks like. For example, if you have the name ‘JOHN SMITH’ with two blank spaces between JOHN and SMITH, it will probably be imperceptible in excel, but if you look for the same with just one space, you will not find it.
You are right, but it’s not spaces, it’s the language! Western apears at the begining of the last name but on where else. How do I remove the western notation, as when I type in excel there is no language notation? Thank You!
You could try copying and performing a ‘special’ paste, pasting just the values of the columns.
If that doesn’t work I would suggest to create a new question about that specific problem.
Regards,
Thank You!!! for all the help… I was able to replace all the spaces with commas and and then all the names with more than one comma with one and then all the commas with one space and everything work perfic!!! Thanks again