VLOOKUP

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:

=IF(ISNA(VLOOKUP(“XYZ”, MyTable, 2, FALSE)),””,VLOOKUP(“XYZ”, MyTable, 2, FALSE))

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:

=IFERROR(VLOOKUP(“XYZ”, MyTable, 2, FALSE),””)

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.

Posted in Uncategorized

18 thoughts on “VLOOKUP

  1. 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.

  2. 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

  3. 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.

  4. MATCH(A2,MyTable,1)=A2 ? If A2 were a string, like “XYZ”, when could this ever work? Missing some INDEX calls.

  5. 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.

  6. 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)))

  7. 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.

  8. 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.

  9. 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.

  10. 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?

  11. 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!

Leave a Reply

Your email address will not be published. Required fields are marked *