VLOOKUP in Excel 2007

35 pts.
Tags:
Excel 2007
Excel VLOOKUP function
Microsoft Excel 2007
VLOOKUP
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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 completely 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

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Newkid47
    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!
    35 pointsBadges:
    report
  • carlosdl
    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.
    68,330 pointsBadges:
    report
  • Newkid47
    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!
    35 pointsBadges:
    report
  • carlosdl
    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,
    68,330 pointsBadges:
    report
  • Newkid47
    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
    35 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following