Excel: vlookup tips

Although Excel now has the more useful xlookup, we still need to use vlookup to maintain backwards compatibility much of the time. Here are some vlookup tips:

=VLOOKUP(
    lookup_value: The value you want to look up,  
    lookup_array: range containing the lookup value,
    return_array: the column number in the range containing the return value,
    approximate_match: TRUE or FALSE (Exact match)
)

Tip 1: Use a named reference for lookup_array

If you use a named reference for the lookup_array, this makes the formula a lot more readable. Also it means you can avoid having to use $ signs.

=VLOOKUP(A2, source_range, 3, FALSE)

Tip 2: For the fourth parameter, always make this FALSE

These days the performance improvement means that making it TRUE doesn't give you much benefit, except to give you the wrong results when the data isn't sorted (noting that data is usually not sorted!).

So just always use FALSE. In fact, the new XLOOKUP assumes you are always doing an exact lookup, since this is what people want.

Tip 4: Replace N/A with blank

If vlookup returns N/A, you can use iferror:
=iferror(vlookup(a,b,c,d),"")

Tip 5: Combine Tip 3 and Tip 4

If you want to remove "0" and ignore N/A, you can use &"" and iferror:
=iferror( vlookup(a,b,c,d) &"" ,"")

Tip 6: Use a vlookup alternative

If you need more flexibility, such as returning something from a column to the left, consider using xlookup.

If you want to return more than one value, consider using the newer filter function. See Excel: Finding the nth last item for more details.