Excel vlookup return not available

Question

i want to fill the name column using vlookup, here is my transaction table

and here is my master file

yes, they're the same number, but why do my vlookup doesn't return the corresponding name based on looked up value ?

does vlookup comply with data type ? like text, or number, or general ?

i have changing the data type, over and over, and return the same "Not Available"

is there anything wrong with my excel 2007 ?

Answer 1

You should use Index/Match like this:

=INDEX(Phonebook!$A$2:$A$45,MATCH(B2,Phonebook!$B$2:$B$45,0))

Your Vlookup doesn't work, because it tried to find value from B2 in first column of range Phonebook!$A$2:$B$45, i.e. Phonebook!$A$2:$A$45

Answer 2

What's wrong is that VLOOKUP is looking for the phone number in the first column, meaning in column A. For 'backwards lookup', you will need to use INDEX and MATCH:

=INDEX(Phonebook!$A$2:$A$45,MATCH(B2,Phonebook!$B$2:$B$45,0))

INDEX is as follows:

=INDEX(Range, Row Number, [Column Number])

It will return the value a cell from the range Range that is on the row number Row Number and column Column Number. I have put Column Number between square brackets because it is optional (you have only 1 column if you have a range that is within A:A for example)

To get the row number, you can use MATCH like the above. It works a bit like VLOOKUP, but instead of returning the value of the matching cell, it returns the row number of the matching cell (or column number if you use it on a horizontal range).

MATCH(B2,Phonebook!$B$2:$B$45,0) thus looks for B2 in the range B2:B45 of the worksheet Phonebook (0 stands for exact match) and gives the row number.

Then, you tell Excel to return the value of the cell from the range Phonebook!$A$2:$A$45 and row number obtained from MATCH.