VLOOKUP Finding matching pairs

Question

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?

Answer 1

You can try this:

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

That way you will convert to number within a formula.

Answer 2

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