XLOOKUP: look up to the left and with a custom message

XLOOKUP is the successor to VLOOKUP. People ask for it because VLOOKUP has three limitations: it only searches the first column, you have to count columns, and you need to handle errors separately. XLOOKUP doesn't have these limitations, and Euro-Office simply supports it.

The structure

=XLOOKUP(lookup_value, lookup_column, result_column, "message if not found")

Example: looking up the invoice amount for Hotel De Haven. The customer names are in column B, the amounts in column G:

=XLOOKUP(A5,Facturen!$B$2:$B$13,Facturen!$G$2:$G$13,"Not found")

Why it's better

• No counting columns: you simply select the result column. If you insert a column later, the formula still works correctly.
• Also looks left: the result column can be to the left of the lookup column.
• Exact by default: you don't need to add FALSE.
• Custom message: the fourth part is the text shown when nothing is found.

Extra options

• Fifth part, the match mode: 0 exact (default), -1 exact or the next smaller value (handy for brackets), 1 exact or the next larger value, 2 with wildcards * and ?.
• Sixth part, the search direction: -1 searches from bottom to top. This way you find a customer's most recent invoice: =XLOOKUP("Bakkerij Van Dijk",B2:B13,F2:F13,"None",0,-1).

Pitfalls

• The lookup column and result column must be the same length: $B$2:$B$13 and $G$2:$G$13, not $G$2:$G$20.
• Avoid using entire columns such as B:B in large files, as this slows the spreadsheet down. See Speed up a slow spreadsheet.
• If you send the file to someone with a very old version of Excel (2016 or earlier), they won't have XLOOKUP. Use VLOOKUP instead.