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 3: Replace 0 with blank

If vlookup returns a "0", this may mean that the cell is actually blank. To avoid this you can do one of the following:

1. In the source data, instead of a blank cell, type in a " ", i.e. a space.

1. In the vlookup formula, you can append an empty string "" to force the result to be text and therefore blank (if you know it is meant to be text, not numerical):
  =vlookup(a,b,c,d) & ""

1. You can turn off the display of 0 for all cells in Excel Options for individual sheets:

4. You can use a complicated "if" formula that uses vlookup twice (hint: this is a pain! Most tutorials tell you to do this because they don't know 1 & 2).
=if(
vlookup(a,b,c,d)=0,
"",
vlookup(a,b,c,d)
)

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.