XLOOKUP searches a range for a value and returns a matching value from another range, with this syntax: =XLOOKUP(lookup_value, lookup_array, return_array). So =XLOOKUP(A2, Vendors!A:A, Vendors!C:C) takes the code in A2, finds it in the Vendors code column, and returns the matching cell from the Vendors category column. It matches exactly by default, it can look to the left, and it does not care which column number the answer sits in. That is why it replaces VLOOKUP for most jobs.
You reach for XLOOKUP the moment two spreadsheets need to agree. You convert a PDF statement or a supplier report into Excel, and now a column of account numbers, item codes, or invoice references needs names, categories, or prices attached from a master list. XLOOKUP stitches the two sheets together with fewer ways to get it wrong than the older function it succeeds.
The XLOOKUP arguments, in plain English
XLOOKUP takes three arguments you use every day and three optional ones you reach for occasionally.
| Argument | What it means | Example |
|---|---|---|
| lookup_value | The thing you are searching for | A2 (an invoice number) |
| lookup_array | The single column or row to search in | Vendors!A:A |
| return_array | The column or row to return a value from | Vendors!C:C |
| if_not_found | Optional. What to show when nothing matches | "Not found" |
| match_mode | Optional. 0 exact (default), -1 or 1 next smaller or larger, 2 wildcard | 0 |
| search_mode | Optional. 1 first to last (default), -1 last to first | 1 |
The important part is what you do not have to supply. There is no col_index_num to count, so you cannot miscount it, and there is no FALSE to remember for an exact match because exact is already the default. The lookup_array and the return_array are two separate ranges, so the value you search does not have to sit in the leftmost column. It can be anywhere.
A worked example
Say you converted a purchase report out of a PDF and landed with item codes in column A and quantities in column B, and you keep a price list on a second sheet named Prices with codes in column A and unit prices in column B.
In C2 you write =XLOOKUP(A2, Prices!$A$2:$A$500, Prices!$B$2:$B$500) and fill it down. Excel takes the code in A2, finds it in the Prices code column, and hands back the matching unit price. Lock both ranges with dollar signs so they do not shift as the formula copies down. If a code has no match, add a fourth argument to say so plainly: =XLOOKUP(A2, Prices!$A$2:$A$500, Prices!$B$2:$B$500, "No price on file"). That is cleaner than wrapping the whole thing in IFERROR the way VLOOKUP forced you to.
How is XLOOKUP better than VLOOKUP?
XLOOKUP fixes the four things that make VLOOKUP fragile. It looks up in one range and returns from another, so it can look to the left. It matches exactly by default, so you cannot forget the exact-match flag. It returns from a named range rather than a counted column number, so inserting a column in the middle of your table does not silently break the result. And it has a built in if_not_found argument for missing values. The tradeoff is availability: XLOOKUP exists only in Microsoft 365 and Excel 2021 and later. If you share files with someone on Excel 2019 or 2016, they will see a #NAME? error, and you should stay on VLOOKUP or INDEX MATCH instead.
| XLOOKUP | VLOOKUP | |
|---|---|---|
| Looks left | Yes | No |
| Default match | Exact | Approximate |
| Breaks if you insert a column | No | Yes |
| Handles "not found" | Built in | Needs IFERROR |
| Works in Excel 2019 and older | No | Yes |
How do I use XLOOKUP to return multiple columns?
Hand XLOOKUP a return_array that is several columns wide and it spills the whole set across. To pull a name, category, and price for one code in a single formula, write =XLOOKUP(A2, Prices!$A$2:$A$500, Prices!$B$2:$D$500). The result fills B, C, and D of the current row in one go. This only works in versions with dynamic arrays (365 and 2021), and you need empty cells to the right for the spill to land, or Excel returns a #SPILL error.
How do I use XLOOKUP with two sheets?
Point the lookup_array and return_array at the other sheet by name. From your working sheet, =XLOOKUP(A2, Masters!$A:$A, Masters!$C:$C) searches the Masters sheet and returns from it, while A2 stays on the sheet you are typing in. For two separate workbooks, open both files and the reference picks up the file name in brackets automatically. Keep both workbooks open, because a closed-workbook XLOOKUP can return errors until the file is opened and recalculated.
How do I use XLOOKUP for a range instead of an exact match?
Set match_mode to -1 or 1 to find the nearest value instead of an exact one, which is how you look up a tier. For a commission or tax table where each row holds a threshold, =XLOOKUP(A2, Brackets!$A$2:$A$8, Brackets!$B$2:$B$8, , -1) finds the largest bracket value that is not above A2 and returns its rate. The -1 tells XLOOKUP to fall back to the next smaller entry when there is no exact hit. Notice the extra comma: it skips the if_not_found argument so match_mode lands in the right position. Unlike VLOOKUP's approximate match, XLOOKUP does not require the lookup column to be sorted ascending, though a sorted table still reads more clearly.
Why is my XLOOKUP returning #N/A?
#N/A means XLOOKUP searched and found no match, and after a PDF conversion the cause is almost never the formula. The top reason is numbers stored as text: the code in A2 looks like 1004 but is text, while the code in the master list is a real number, so they never match. Fix the type first with our guide to converting text to numbers. The second reason is stray spaces that a conversion leaves on the end of a value, which you clear by removing extra spaces with TRIM before you compare. Only after ruling those two out should you check that you are actually searching the right column.
| Error | Usual cause | Fix |
|---|---|---|
| #N/A | No match: text vs number, or trailing spaces | Fix the data type, TRIM the spaces |
| #NAME? | XLOOKUP not available in this Excel version | Use VLOOKUP or INDEX MATCH on 2019 and older |
| #SPILL | No empty room for a multi-column result | Clear the cells to the right of the formula |
| #REF! | lookup_array and return_array are different sizes | Make both ranges the same height |
Where XLOOKUP fits in a conversion workflow
The pattern is always the same. You convert the PDF to Excel, you get raw codes or references with no meaning attached, and XLOOKUP maps each one to the name, category, or price it stands for. When the source is a bank or supplier document, it is faster to turn the PDF statement into clean spreadsheet rows first than to retype the figures and hope the references line up. Once the lookup runs clean, do the arithmetic check: total each category with SUMIF and confirm the sum ties to the printed total on the original document. A lookup that returns a value for every row still proves nothing if half the rows never made it out of the PDF.
The short version
Use =XLOOKUP(lookup_value, lookup_array, return_array), lock the two arrays with dollar signs, and add a fourth argument to handle missing values gracefully. It looks left, matches exactly by default, and survives an inserted column, so it is the better choice than VLOOKUP whenever your readers are on Microsoft 365 or Excel 2021. If you get #N/A on converted data, check for numbers stored as text and stray spaces before you touch the formula.