As you know, the fourth argument of the VLOOKUP worksheet function determines whether VLOOKUP finds an exact match or an approximate match. If you set the last argument to False, and there is no exact match in the lookup list, the formula will return #N/A!.
In Excel 2003, the best way to capture this error, is to use the ISNA worksheet function, such as in this example:
This checks to see if the formula returns the NA error and, if so, returns an empty string (to look like a blank cell). There is a big downside to this: For each cell this is used in, Excel has to calculate the VLOOKUP function twice. If you have a lot of them, it can really slow things down. Unfortunately, I don’t know of a better way.
In Excel 2007, however, a new worksheet function was introduced. IFERROR not only shortens your formulas, but can speed up calculations. In 2007, it looks like this:
It only evaluates VLOOKUP once, which is nice. The downside to this function is that it masks errors other than NA. For example, if you misspelled MyTable, you would not receive a #NAME? error, you would get nothing and possibly reach the wrong conclusion. So don’t make an mistakes. :)
Thanks Mark.
Hi,
nice tip about the IFERROR function. Thanks. :-) But as I work with Excel 2003, I searched for a suitable alternative. I found, how I can add IFERROR also in Excel 2003. Follow the link http://www.stutzman.org/index.php/2008/04/02/iferror-for-excel-2003.
Smurf has another approach for earlier XL:
http://smurfonspreadsheets.wordpress.com/2008/07/03/iferror/
…mrt
There’s an assumption that there’d be many formulas of the form
=IF(ISNA(VLOOKUP(…)),””,VLOOKUP(…))
If there were many matches, the matches would each require two VLOOKUP calls, thus slowing down recalc.
There’s a work-around that involves augmenting the table. It assumes there are no cells in the first column of my table that contain zero length strings. I DON’T MEAN BLANK CELLS!
Add an extra row at the top of MyTable and enter the formula =”” in each cell in that row. Then you could use formulas like
=INDEX(INDEX(MyTable,0,2),LOOKUP(1E+300,MATCH({“”,”XYZ”},INDEX(MyTable,0,1),0)))
While this implicitly uses two MATCH calls, one for each of its array first argument, the first value is matched immediately in the new first row. Only the second value requires a nontrivial matching operation. The LOOKUP call will return the MATCH for the second value if it’s a number, else 1 for the first row. This also allows error values in the result column of MyTable to flow through. Heck, it even works in Excel 2007.
It recalcs faster than IF(ISNA(…)…) and isn’t as limited as IFERROR.
Nick burns posted another alternate approach for 2003 and earlier on a post I wrote up recently as well, using CountIf:
http://www.excelguru.ca/blog/2009/03/18/dealing-with-vlookup-and-getpivotdata-errors/
Ken
I always add this UDF to spreadheets with versions prior to 2007. Same concept. Care should be taken that logical errors are not being hidden.
Public Function ErrorTest(vValue1 As Variant, vValue2 As Variant, _
Optional vValue3 As Variant, Optional vValue4 As Variant, _
Optional vValue5 As Variant, Optional vValue6 As Variant) As Variant
‘returns the first value that is not an error
‘returns the last value if all are errors
‘to return “n/a” as a default, set it as the last parameter;
‘eg =ErrorTest(1/0, 5/0, “n/a”)
‘eg instead of =IF(ISERROR(VLOOKUP(A2, $X$2:$Z$2000, 3, FALSE)), VLOOKUP(A2, $X$2:$Z$2000, 3, FALSE), 0)
‘ use =ErrorTest(VLOOKUP(A2, $X$2:$Z$2000, 3, FALSE), 0)
If Not WorksheetFunction.IsError(vValue1) Then
ErrorTest = vValue1
ElseIf Not WorksheetFunction.IsError(vValue2) Then
ErrorTest = vValue2
ElseIf IsMissing(vValue3) Then
ErrorTest = vValue2
ElseIf Not WorksheetFunction.IsError(vValue3) Then
ErrorTest = vValue3
ElseIf IsMissing(vValue4) Then
ErrorTest = vValue3
ElseIf Not WorksheetFunction.IsError(vValue4) Then
ErrorTest = vValue4
ElseIf IsMissing(vValue5) Then
ErrorTest = vValue4
ElseIf Not WorksheetFunction.IsError(vValue5) Then
ErrorTest = vValue5
ElseIf IsMissing(vValue6) Then
ErrorTest = vValue5
Else
ErrorTest = vValue6
End If
End Function
VLOOKUP is very slow when using the exact match option. Better to sort the list first and then use:
=IF(MATCH(A2,MyTable,1)=A2,INDEX(MyTable,MATCH(A2,MyTable,1)),”Not Found”)
The two matches on a sorted list are (with any significant table size) faster than a single vlookup.
MATCH(A2,MyTable,1)=A2 ? If A2 were a string, like “XYZ”, when could this ever work? Missing some INDEX calls.
fzz’s right. You’d need something more like this:
=IF(INDEX(MyTable1,MATCH(A2,MyTable1))=A2,INDEX(MyTable2,MATCH(A2,MyTable1)),”No Match”)
MyTable1 would be a single column range you are matching and MyTable2 would be the single column range with the corresponding return value. They would both have to have the same number of rows, or at least start in the same row.
The other benefit of this, besides much faster results (try it vs. a vlookup where you’re looking up 50k items against a 50k table and you’ll see what I mean), is that MyTable2 can be in a column before MyTable1. So you could match against column B and return the item in column A.
But that doesn’t address Dick’s original problem because you can still return #N/As. So you’d really need this:
=IFERROR(IF(INDEX(MyTable1,MATCH(A2,MyTable1))=A2,INDEX(MyTable2,MATCH(A2,MyTable1)),”No Match”),”No Match”)
or, before XL07, this:
=IF(ISERROR(INDEX(MyTable1,MATCH(A2,MyTable1))),”No Match”,IF(INDEX(MyTable1,MATCH(A2,MyTable1))=A2,INDEX(MyTable2,MATCH(A2,MyTable1)),”No Match”))
Good luck remmebering that syntax. I have to do massive lookups a lot so I have a userform that writes the formula.
zach, the other problem Dick mentioned was the limitation that 2007’s IFERROR traps ALL errors, not just #N/A. If you try to use IFERROR on MATCH’s result, what’s the result when MATCH returns #N/A? If you use an augmented table, as I suggested before, you could use a 2007 formula like
=INDEX(MyTable,IFERROR(MATCH(“XYZ”,INDEX(MyTable,0,1),0),1),2)
The problem with MATCH or VLOOKUP calls against sorted tables is that it’s still possible for MATCH or VLOOKUP to return errors when the lookup value is less than the value in the first column of the first row of the table. IOW, bullet proofing requires
=IF(x<INDEX(MyTable,1,1),””,
IF(INDEX(MyTable,MATCH(x,INDEX(MyTable,0,1)),1)x,””,
INDEX(MyTable,MATCH(x,INDEX(MyTable,0,1)),1)))
Jan Karel’s idea is correct, but it does need the additional INDEX.
For Dick’s original question the fastest way to do VLOOKUP on SORTED data when there may not be an exact match is:
=IF(VLOOKUP(“XYZ”, MyTable, 1, TRUE)=”XYZ”,VLOOKUP(“XYZ”, MyTable, 2, TRUE),””)
The first approximate VLOOKUP tests to see if the lookup value exists in the table by checking if the value returned from the lookup column is the same as the value you are looking for. If it is then it exists so you can do the VLOOKUP again to return the value from the answer column, but otherwise return “”.
You can use the same idea with INDEX and MATCH.
But the data MUST be sorted.
Yep, it does need the extra INDEX of course.
I submitted the reply too fast. Well spotted guys.
Great tip, thanks.
Charles,
Nice. Wish I would’ve thought of that before resorting to the INDEX/MATCH pair. But you would still need an error trap for the #N/A that you get if you are looking for a value smaller than the smallest value in your sorted list.
Zach,
If there is a real-world possibility of looking for a value smaller than the smallest value in the list and you want to trap the #N/A for that special case then you are correct: either add an error trap or a dummy very small entry in the list.
Yes, the last argument is not mandatory, however I always tend to set it as “false”. It’s easy then to get rid of those #N/A! simply by finding them and replacing with blank field.
This tutorial also helped me much to understand how vlookup works:
http://www.myhowtoos.com/en/excel-howtoos/84-how-to-match-values-in-excel-using-vlookup
I am trying to write what i think is a simple vlookup IFERROR(VLOOKUP(“XYZ”, MyTable, 2, FALSE),””) and it returns #NAME? error. Not all the values the formula is looking up is in the table, is there a better way to write this formula?
Sally, are you using a version of Excel prior to 2007? Sounds to me like IFERROR isn’t being recognised otherwise you wouldn’t see the error as the IFERROR would pick it up. Wow that sounded complicated!
Good one Justin. Sally, if you’re using 2003 or earlier, use