VLOOKUP Finding matching pairs

1
votes

I currently am having issues using VLOOKUP to find matches within columns.

In column A I have Customer Names and in Column B I have corresponding phone numbers to each customer.

In Column D I have a List of possible customer names, and I want to populate column E with their phone number if ones exists.

The current forumula I'm using is: 

=VLOOKUP(D2,$A$2:$B$10706,2,FALSE)

Where D2 is the first 'Customer Name'

$A$2:$B$10706 being the Full range of columns A+B

Where index 2 is the phone number to return on a match

FALSE denoting that only exact matches be returned.

I Keep receiving an #N/A error even for exact matches I've gone through and manually found, and I'm not sure why.

Can anyone offer some direction?

2
excelexcel-formulavlookup
Could you please give as a glimpse of your data? Screenshots maybe? Probably is data related. There is no exact match to be found or otherwise it should have found something. Please recheck your data. - Blenikos
Ar you positive there are no leading or trailing spaces? Without sample data it's hard to judge why this could be the case. - Robin Gertenbach
Hard to say without a sample of your data, given you are using exact matching have you checked for simple issues on the target like whitespaces/newlines? - jhole89
Try equating your matches and see if it returns TRUE like =D2=A35 where A35 is a supposed match to D2. - Scott Craner
Formula Evaluation will not help in this case. VLOOKUP is calculated in one step and you will not be able to see why it fails - Blenikos

2 Answers

4
votes

Your problem as far as I can tell from the screenshot you gave us is that you have in one column numbers and on the other numbers stored as text (see the small green arrow on the top left side of the cell).

Double click the cell and then enter to make it from text to number and then vlookup will work.

To change the whole column and remove "number stored as text" from all cells you can do the following small "hack:

  • Copy the whole column
  • Go to an empty column Use
  • Pastespecial->Operation->Add

enter image description here

0
votes

You can try this:

=VLOOKUP(D2*1,$A$2:$B$10706,2,FALSE)

That way you will convert to number within a formula.