How to Use XLOOKUP Excel

What this does

XLOOKUP replaces VLOOKUP and HLOOKUP with one function that looks left or right, top or bottom, and defaults to an exact match instead of an approximate one. You give it the value to find, the column to search, and the column to return — no column-index counting, no FALSE flag to remember. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “use xlookup excel”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a data step that keeps your analysis trustworthy useful in real work: repeatable, auditable, and not dependent on memory or luck.

A worked example

Put SKU-1, SKU-2 and SKU-3 in A2:A4. Put the numeric prices 0 and 19.5 in B2:B3 and leave B4 empty. With SKU-2 in E2, =XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"not found") returns 19.5; SKU-1 returns the legitimate price 0; SKU-9 returns not found. SKU-3 exists but its return cell is empty, so the basic formula displays 0 rather than the not-found text. To preserve that distinction, use =XLOOKUP(E2,$A$2:$A$4,IF($B$2:$B$4="","",$B$2:$B$4),"not found"). SKU-3 now produces an empty string while SKU-1 still returns numeric 0. The empty string is a formula result, not a truly empty output cell. Separate ranges remove the need to count a return-column index. Check the missing-key policy and the blank-return policy separately so a lookup can distinguish an absent product, a missing price and a genuine zero. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.

In Google Sheets

Function availability: XLOOKUP: Excel 2021+. These are Microsoft's version markers; earlier perpetual releases do not include these functions. Check the actual product and update channel before using the formula. A marker does not establish an identical Web/Mac workflow or Google Sheets support. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. For “use xlookup excel”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.

Common mistakes

• Selecting ranges of different heights for lookup_array and return_array, which throws a #VALUE! error.
• Assuming approximate match like VLOOKUP — XLOOKUP defaults to exact, so set match_mode to -1 or 1 only when you need the nearest value.
• Leaving the source ranges relative while copying down, which moves the lookup table and can lose matches.
• Replacing every zero with an empty string to hide blank prices; that also hides genuine numeric zeros. Test the return cells for blanks instead.
• Using binary search_mode 2 on an unsorted ascending range or -2 on an unsorted descending range; Microsoft warns that this can return invalid results.

Frequently asked questions