#1
|
|||
|
|||
Vlookup
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 |
#2
|
||||
|
||||
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. |
#3
|
|||
|
|||
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! |
#4
|
||||
|
||||
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)) |
#5
|
|||
|
|||
Thank you so much! You are da bomb!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using Vlookup & IF together | thelauncher | Excel | 5 | 08-25-2013 11:32 PM |
Can i do this with a VLookup? | foodstudent | Excel | 1 | 01-21-2011 12:34 AM |
Using IF & VLOOKUP together | junction | Excel | 7 | 11-18-2010 05:15 AM |
Help with VLOOKUP | sakhtar | Excel | 2 | 07-24-2010 07:39 PM |
vlookup question | Tony G | Excel | 9 | 03-28-2009 11:16 PM |