I’m using VLOOKUP but not getting the correct results.

Sheet1 (Reference Table)
Column A = License No.


Column B = State

Sheet2
Column A = License No.
I have to pull in the states that correspond to each license number.
Below is my formula. Some return values are correct are some are not. I cannot figure out why this is happening.
=VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2)
Your help is greatly appreciated

Hi Karen,

You also need to specify a fourth argument, which tells it to do an exact look up:

=VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2,0)

If you omit the fourth argument (it is optional) then VLOOKUP does an approximate match. This can be great when your data is sorted, because the formula calculates more quickly.

__________________
Colin

RAD Excel Blog

Thank you!

It worked out great! Using the below example of the formula, what can be done to the formula so it elimates the #N/A error?
Is it possible to replace the #N/A with the words "no match"?

=VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2,0)

Thank you for your help!

Hi Karen,

Sure, if you are using Excel 2007 or later you can do it like this:

=IFERROR(VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2,0),"no match")

If you are using Excel 2003 or earlier then you can do it like this:

=IF(ISNA(VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2,0)),"no match",VLOOKUP(A2,'Sheet1'!$A$2:$B$141,2,0))

__________________
Colin

RAD Excel Blog

Thank you so much! You are da bomb!