Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All functions

XLOOKUP

Lookups & references

The modern lookup — find a value and return another, in any direction, with a clean not-found fallback.

Difficulty

Amateur

Excel file

1What is it?

XLOOKUP searches one range for a value and returns the matching item from another range. It replaces VLOOKUP and HLOOKUP: the lookup and return columns can be in any order, it defaults to an exact match, and it takes a built-in value for when nothing is found.

2What it looks like

XLOOKUP(lookup, lookup_array, return_array, [if_not_found])

lookup: The value to find (e.g. an account code).
lookup_array: The column/row to search in.
return_array: The column/row to return the result from.
[if_not_found]: What to return when there's no match — use this instead of wrapping in IFERROR.

3When you use it

Map an account code to its name, or a SKU to its price.
Pull a customer's terms, owner, or region into a report.
Return the last matching value by searching bottom-up (search_mode -1).

4See it in action

Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.

Start simple · 1 of 2

Look up an account code and return its name.

=XLOOKUP("4100", A2:A4, B2:B4, "Not found")Service revenue

	A	B	C
1	Code	Name	Result
2	4000	Product revenue	Service revenue
3	4100	Service revenue
4	5000	Cost of goods sold

The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.

Look up code

5Common errors

#N/ANo exact match was found.

Fix: Add the if_not_found argument, or check for stray spaces (wrap the lookup in TRIM).

#VALUE!lookup_array and return_array are different sizes.

Fix: Make both ranges the same height (or width).

#REF!A referenced column was deleted.

Fix: Re-point the return_array at a valid range.

6Better functions & alternatives

INDEX + MATCH — Use in Excel versions without XLOOKUP, or for two-dimensional lookups.
VLOOKUP — Still everywhere, but only looks right and breaks when columns are inserted.

Want XLOOKUP already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.

Learn the moves here — or let Wauvel run them on your numbers.

Get my free report →