Lookup Functions in Excel

XLOOKUP: The Modern Lookup

XLOOKUP replaces VLOOKUP and HLOOKUP in modern Excel. It searches a range for a match and returns a corresponding value from another range. The syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Only the first three arguments are required. lookup_value is what you are searching for. lookup_array is where to search. return_array is where to find the answer.

=XLOOKUP(A2, Products!A:A, Products!B:B)

This searches for the value in A2 within column A of the Products sheet and returns the corresponding value from column B. If A2 contains a product ID, the formula returns the product name.

Why XLOOKUP Beats VLOOKUP

VLOOKUP has four structural weaknesses that XLOOKUP eliminates.

First, VLOOKUP can only look rightward. The lookup column must be the leftmost column in the table array. XLOOKUP has no directional constraint — the return array can be to the left of the lookup array.

Second, VLOOKUP uses a column index number. If someone inserts a column into the table, the index shifts and the formula returns the wrong column. XLOOKUP references the return array directly, so column insertions do not break it.

Third, VLOOKUP defaults to approximate match, which returns wrong results silently when the data is not sorted. XLOOKUP defaults to exact match.

Fourth, VLOOKUP returns #N/A when no match is found, and that error propagates through any formula that references the cell. XLOOKUP has a built-in if_not_found argument.

The if_not_found Argument

=XLOOKUP(A2, Products!A:A, Products!B:B, "Not found")

If A2 does not match any value in the lookup array, the formula returns "Not found" instead of #N/A. This is not cosmetic. A cell containing #N/A causes every downstream formula — sums, charts, conditional formatting — to also return #N/A. A text fallback keeps the workbook functional.

Match Modes

The match_mode argument controls how Excel matches the lookup value:

• 0 (default): Exact match. Returns #N/A if not found.
• -1: Exact match or next smaller item. Useful for tiered lookups like tax brackets.
• 1: Exact match or next larger item.
• 2: Wildcard match. Supports * and ? in the lookup value.

The tiered lookup use case is powerful. Suppose you have a commission table where deals under $10,000 earn 5%, deals under $50,000 earn 7%, and larger deals earn 10%. With match_mode = -1 and a sorted lookup table, XLOOKUP finds the correct tier without nested IF statements.

Search Modes

The search_mode argument controls the search direction:

• 1 (default): Search first to last.
• -1: Search last to first. Useful when the lookup array contains duplicates and you want the most recent match.
• 2: Binary search ascending. Requires sorted data. Much faster on large ranges.
• -2: Binary search descending. Requires sorted descending data.

Binary search modes are dramatically faster on large datasets — O(log n) vs O(n) — but they require the lookup array to be sorted. If the data is not sorted, binary search returns incorrect results without warning.

INDEX-MATCH: The Flexible Alternative

INDEX-MATCH is the predecessor to XLOOKUP and remains useful in two scenarios: when you need a two-way lookup (finding a value at the intersection of a row and column), and when working in older Excel versions that lack XLOOKUP.

How INDEX-MATCH Works

INDEX returns a value from a range at a specified position. MATCH finds the position of a value within a range. Combined, they look up a value and return the corresponding result.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

MATCH searches lookup_range for lookup_value and returns the relative position. INDEX uses that position to fetch the value from return_range.

=INDEX(Products!B:B, MATCH(A2, Products!A:A, 0))

This is equivalent to the XLOOKUP example from the previous chapter. MATCH finds the row where A2 appears in Products column A, and INDEX returns the value from Products column B at that row.

Two-Way Lookup

The real power of INDEX-MATCH emerges in two-way lookups. Suppose you have a table where rows are products and columns are months, and you need the value at the intersection of a specific product and month.

=INDEX(C2:N10, MATCH(A2, B2:B10, 0), MATCH(B1, C1:N1, 0))

The first MATCH finds the row (product). The second MATCH finds the column (month). INDEX returns the value at that intersection. XLOOKUP can do this too, but it requires nesting one XLOOKUP inside another, which is harder to read.

MATCH Match Types

The third argument of MATCH controls the match type:

• 0: Exact match. The most common choice.
• 1: Less than or equal. Requires ascending sorted data. Finds the largest value that does not exceed the lookup value.
• -1: Greater than or equal. Requires descending sorted data.

The 1 and -1 modes enable tiered lookups, just like XLOOKUP's match_mode -1 and 1. The data must be sorted or the results are unpredictable.

Why INDEX-MATCH Is More Flexible Than VLOOKUP

INDEX-MATCH separates the lookup and return ranges entirely. The return range can be any size and any position relative to the lookup range. This means you can look left, look right, look up, or look down — the function does not care about direction.

It also handles row and column insertions gracefully. If a row is inserted above the lookup range, both MATCH and INDEX adjust their references automatically because they use relative positions, not fixed column indices.

When to Use INDEX-MATCH Over XLOOKUP

Use INDEX-MATCH when you need a two-way lookup, when you are working in Excel 2019 or earlier (which lack XLOOKUP), or when you need to return an array of values (INDEX can return entire rows or columns with a zero in the row or column argument). For simple one-way lookups in modern Excel, XLOOKUP is clearer and has better defaults.

Handling Missing Values and Edge Cases

Lookups fail when the lookup value does not exist in the lookup array. The failure mode is #N/A, and if you do not handle it, the error cascades through every formula that references the result. This chapter covers the strategies for making lookups robust.

IFNA: Targeted Error Handling

IFNA catches only #N/A errors. It is more precise than IFERROR because it lets other errors — #REF!, #VALUE!, #DIV/0! — surface as visible problems.

=IFNA(XLOOKUP(A2, Products!A:A, Products!B:B, "Not found"), "Not found")

In this example, XLOOKUP already has a fallback for not-found, so IFNA is redundant. But when using INDEX-MATCH or VLOOKUP (which lack a built-in fallback), IFNA is essential:

=IFNA(INDEX(Products!B:B, MATCH(A2, Products!A:A, 0)), "Not found")

XLOOKUP's Built-in Fallback

XLOOKUP's fourth argument handles not-found cases directly:

=XLOOKUP(A2, Products!A:A, Products!B:B, "Unknown", 0)

The fourth argument ("Unknown") is returned when no match is found. The fifth argument (0) specifies exact match. This is cleaner than wrapping in IFNA because the fallback is part of the function's design.

Dealing with Duplicates

When the lookup array contains duplicate values, XLOOKUP returns the first match by default. If you need the last match, use search_mode = -1:

=XLOOKUP(A2, Data!A:A, Data!B:B, "Not found", 0, -1)

This searches from the bottom up and returns the last occurrence. This is useful for transaction logs where the same customer appears multiple times and you want the most recent record.

Wildcard Lookups

XLOOKUP supports wildcard matching with match_mode = 2:

=XLOOKUP("*" & A2 & "*", Products!A:A, Products!B:B, "Not found", 2)

The * wildcard matches any sequence of characters. This finds partial matches — useful when the lookup value is embedded in a longer string, like finding a product SKU within a product description.

Data Type Mismatches

A common failure: the lookup value is a number stored as text, or the lookup array contains numbers stored as text. Excel does not consider 123 (number) and "123" (text) equal. The lookup returns #N/A even though the values look identical.

Fix this by ensuring consistent data types. Use VALUE() to convert text to numbers, TEXT() to convert numbers to text, or the Text to Columns wizard to reformat an entire column. In Power Query (covered later), set the column type explicitly during import.

Trailing Spaces

Another silent failure: trailing spaces. "West" and "West " are different strings. The lookup fails even though the values appear identical. Use TRIM() to remove leading and trailing whitespace:

=XLOOKUP(TRIM(A2), Products!A:A, Products!B:B, "Not found")

Better yet, clean the data at the source so the lookup array does not contain stray spaces.