XLOOKUP in Excel for Financial Models

XLOOKUP is a single function that searches a lookup array and returns the matching value from a return array. It replaces VLOOKUP, HLOOKUP, and most INDEX MATCH constructs with one syntax, adds built in error handling, and looks in any direction by default. For financial models it gives you cleaner, more auditable assumption lookups with fewer moving parts.

Syntax and arguments

The full signature is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The first three arguments are required. The optional arguments cover what you would otherwise bolt on with IFERROR and sorting.

if_not_found returns a value of your choice instead of #N/A when no match exists. match_mode accepts 0 for exact (default), -1 for exact or next smaller, 1 for exact or next larger, and 2 for wildcard. search_mode accepts 1 for first to last (default), -1 for last to first, and 2 or -2 for binary search on sorted data.

Worked example: looking up a discount rate by rating

Say credit ratings sit in B2:B6 and the corresponding discount rates in C2:C6. You want the rate for the rating held in F1, and a clear flag if the rating is not in the table.

1. Enter =XLOOKUP(F1, B2:B6, C2:C6, "no match", 0).
2. F1 is the lookup value, B2:B6 is searched, and the matching value from C2:C6 is returned.
3. If F1 holds "BBB" and that sits in the second row of the range, XLOOKUP returns the second rate, for example 0.085.
4. If F1 holds a rating not present, the fourth argument returns "no match" instead of #N/A, so the error does not cascade.

XLOOKUP("BBB", B2:B6, C2:C6, "no match", 0) returns 0.085.

Why it fits modeling work

Lookup and return arrays are passed as separate ranges, so XLOOKUP looks left, right, up, or down with no offset math. Inserting columns inside the table cannot break it because there is no hardcoded column index.

• Built in if_not_found removes the need to wrap the formula in IFERROR.
• Default exact match means no accidental approximate lookups on unsorted data.
• search_mode -1 returns the last match, useful for finding the most recent period that meets a condition.
• Return a whole row or column by pointing return_array at a multi cell range, handy for pulling an entire scenario column at once.

Two way lookups and availability

For a grid lookup, nest XLOOKUP inside XLOOKUP. =XLOOKUP(rowkey, rowlabels, XLOOKUP(colkey, collabels, datagrid)) resolves the column first, then the row, returning a single cell from the grid.

XLOOKUP is available in Excel for Microsoft 365 and Excel 2021 and later. If your model must open in Excel 2019 or earlier, those cells will show #NAME?, so fall back to INDEX MATCH for files shared with users on legacy versions.