How to Use XLOOKUP in Excel: The Lookup That Replaced VLOOKUP (With Worked Examples)

CodexFuse Team · 2026-10-28T00:46:22.760Z · 14 min read

XLOOKUP replaces VLOOKUP with a simpler six-argument formula. Learn the syntax with worked examples: lookups to the left, multiple columns, custom not-found messages, and two-condition matches.

Learning how to use XLOOKUP in Excel takes about ten minutes, and it replaces the lookup formula most people still struggle with. XLOOKUP is Excel's newer lookup function: you give it a value to find, the column to search, and the column to return, and it finds the match. It searches in any direction, returns more than one column at a time, and lets you write your own message when nothing matches. This guide works through one small shop inventory table from start to finish, so every formula below is one you can type into your own sheet and check. If your data needs to leave the spreadsheet first, the Excel-to-JSON converter turns a sheet into data another app can use.

What Is the Fastest Way to Use XLOOKUP in Excel?

The fastest way to use XLOOKUP in Excel is to type the three required arguments and press Enter: the value to find, the range to search, and the range to return from. The full pattern is =XLOOKUP(lookup_value, lookup_array, return_array). The three optional arguments handle the not-found message, the match type, and the search direction.

1. Put your lookup value in a cell, for example a product code in G2.
2. Click the cell where the answer should go and type =XLOOKUP(G2, A2:A7, D2:D7).
3. Press Enter. Excel searches column A for the code in G2 and returns the matching value from column D.

What Is XLOOKUP in Excel?

XLOOKUP is a lookup function that searches a range for a value and returns the matching result from a separate range. It arrived with Excel 2021 and Microsoft 365, and it is also in Excel 2024, Excel for the web, and the Excel mobile apps. It is not available in Excel 2019, Excel 2016, or earlier versions, so anyone opening your file in an older version will see a #NAME? error instead of a result. Google Sheets also supports XLOOKUP, so the same formulas work there.

The function has six arguments, but only the first three are required. The last three are optional, and you skip one by leaving it blank between two commas. DataCamp's XLOOKUP guide uses the same six-argument breakdown, and Microsoft's own function reference lists the same options.

One rule to remember before the examples: the lookup array and the return array must be the same size. If one covers six rows and the other covers five, Excel returns #VALUE! instead of an answer.

How Do You Write Your First XLOOKUP?

You write your first XLOOKUP by pointing at three ranges: what to find, where to look, and what to return. Every example in this guide uses the same small inventory table, so you can follow along in a blank sheet.

To find the price of the drill bit set, click an empty cell and type =XLOOKUP("S203", A2:A7, D2:D7). Excel searches column A for S203, finds it in row 4, and returns $27 from column D. Typing the code directly into the formula works, but it is better to keep the lookup value in a cell. Put S205 in G2 and type =XLOOKUP(G2, A2:A7, B2:B7), and the cell shows "LED Bulbs (4-pack)". Change G2 to S202 and the answer changes to "Paint Roller" without touching the formula.

If you need the same lookup for a whole list, lock the two ranges with dollar signs before copying down: =XLOOKUP(G2, $A$2:$A$7, $D$2:$D$7). The dollar signs are absolute references, so the ranges stay put while the lookup value moves down a row at a time. DataCamp's guide makes the same point: without the dollar signs, the search range slides down with the formula and the answers go wrong.

Before you build the table, check where your data comes from. If a supplier sends the list as a CSV export, first learn how to open a CSV file in Excel without Excel mangling the codes, because a code like S201 pasted as text will not match the number 201.

How Do You Return a Value From a Column on the Left?

You return a value from a column on the left by pointing the return array at that column, which VLOOKUP cannot do at all. VLOOKUP only searches the leftmost column of its range and returns a value to the right, so looking left meant rearranging the data or adding a helper column. XLOOKUP has no such restriction because the search range and the return range are separate arguments.

Using the same inventory table, suppose you know the item name and want its SKU. Type =XLOOKUP("Paint Roller", B2:B7, A2:A7). Excel searches the item names in column B, finds "Paint Roller" in row 3, and returns S202 from column A, which sits to the left of the search column. How-To Geek's comparison of the two functions calls this the clearest reason to switch: the return column can sit anywhere, including to the left.

How Do You Show Your Own Message Instead of #N/A?

You show your own message instead of #N/A by filling in the fourth argument, if_not_found. By default XLOOKUP returns the #N/A error when it cannot find a match, which is honest but ugly in a sheet other people read. The fourth argument replaces that error with whatever you choose.

To check whether a code exists in the inventory, type =XLOOKUP("S299", A2:A7, D2:D7, "Not in stock"). S299 is not in column A, so the cell shows "Not in stock" instead of #N/A. You do not need an IFERROR or IFNA wrapper around the formula, which is what VLOOKUP users had to do. If the result feeds into a sum, use 0 instead of text, and if you want the cell to stay empty, use two quotation marks with nothing between them: "".

One warning: if_not_found only catches missing matches. If your lookup array and return array are different sizes, XLOOKUP still returns #VALUE!, and no message you write will stop that. Check the range sizes first when a formula that looks right returns an error.

How Do You Return Multiple Columns With One XLOOKUP?

You return multiple columns with one XLOOKUP by pointing the return array at several columns instead of one. Excel then spills the whole matching row across as many cells as needed, with the formula living in only the first cell. This only works in the versions that support dynamic arrays, which is Microsoft 365, Excel 2021, and Excel 2024.

To pull the item, aisle, and price for one SKU in a single formula, type =XLOOKUP("S204", A2:A7, B2:D7). The return array B2:D7 is three columns wide, so Excel returns "Work Gloves", 2, and $14, spilling into the formula cell and the two cells to its right. Click one of the spilled cells and the formula bar shows a greyed-out copy of the original formula, not a formula of its own.

The cells to the right must be empty, or Excel shows a #SPILL! error. Clear whatever is in the way, move the formula, or return a single column instead. Spilled results also cannot go inside an Excel table, run past the edge of the sheet, or overlap merged cells.

How Do You Match on Two Conditions?

You match on two conditions by searching for the number 1 in a combined TRUE/FALSE calculation. Each comparison gives TRUE or FALSE for every row, and multiplying two comparisons gives 1 only where both are true. XLOOKUP then looks for that 1 and returns the matching row.

Two items in the inventory sit in aisle 3, so the aisle alone is not enough to find the drill bit set. The formula =XLOOKUP(1, (C2:C7=3)*(B2:B7="Drill Bit Set"), A2:A7) checks both columns at once and returns S203. The same pattern works whenever names repeat: first name plus city, product plus size, or customer plus month. SupportYourTech's guide uses exactly this trick for duplicate first names, and it is the standard answer when one condition is not enough.

How Do You Find the Closest Match?

You find the closest match by setting match_mode to -1 or 1, which turns an exact lookup into a band lookup. Set it to -1 and XLOOKUP returns the largest value that is less than or equal to your lookup value. Set it to 1 and it returns the smallest value that is greater than or equal to it. This is how commission tiers, grade bands, tax brackets, and shipping rates are matched in real sheets.

Take a simple commission table: sales from $0 earn 5%, from $5,000 earn 8%, and from $10,000 earn 12%. A rep with $7,500 in sales matches no row exactly, so the formula =XLOOKUP(7500, $A$2:$A$4, $B$2:$B$4, , -1) returns 8%, because $5,000 is the largest threshold that does not exceed $7,500. A rep with $12,000 returns 12%, and a rep with $3,000 returns 5%. Use 1 instead of -1 when a value should round up to the next band, for example a 7-pound parcel that must ship at the 10-pound rate.

Approximate matching assumes the lookup range is sorted, ascending for -1 and descending for 1. If it is not sorted, XLOOKUP can return a wrong result without any error, which is worse than an error. Always check the sort order before relying on a band lookup.

How Do You Find the Most Recent Entry?

You find the most recent entry by setting search_mode to -1, which makes XLOOKUP search from the bottom of the range up instead of from the top down. By default XLOOKUP returns the first match it finds, so a customer who appears three times in an order log gives you their oldest order. Searching from the bottom gives you their latest one.

Say an order log lists a customer called Rana three times: $120 on March 4, $64 on April 9, and $148 on May 22, oldest at the top. The formula =XLOOKUP("Rana", A2:A10, C2:C10, , 0, -1) returns $148, the latest order. Without the -1 you would get $120. The empty space between the two commas skips if_not_found, and the 0 keeps an exact match. The same trick works for the latest price, the latest status, or the latest note on any repeated value.

How Do You Look Up a Row and a Column at the Same Time?

You look up a row and a column at the same time by putting one XLOOKUP inside another. The inner XLOOKUP finds the product's row and returns all four monthly figures, and the outer XLOOKUP searches the month headers across the top and picks the right figure from that set. This also shows that XLOOKUP works on rows as well as columns, so it replaces HLOOKUP too.

In this grid the product names sit in A2:A5 (Desk, Chair, Lamp, Shelf) and the months run across B1:E1. With "Lamp" in H2 and "Mar" in H3, the formula =XLOOKUP(H3, B1:E1, XLOOKUP(H2, A2:A5, B2:E5)) returns 58, the number where the Lamp row meets the March column. Change either cell and the answer updates.

XLOOKUP vs VLOOKUP: Which Should You Use?

XLOOKUP is the better choice whenever everyone who opens the file has Microsoft 365, Excel 2021, or a newer version. VLOOKUP is the right choice when anyone on the file still uses Excel 2019 or older, because XLOOKUP cells will show #NAME? for them. The table below is the whole comparison in one place.

The default match is the difference most people feel first. VLOOKUP's default is an approximate match, so forgetting the final FALSE argument is the classic VLOOKUP mistake: it returns a plausible-looking wrong answer instead of an error. XLOOKUP defaults to an exact match, so a miss shows up as #N/A and you notice it. How-To Geek's comparison makes the same point: with XLOOKUP you point at the column you want, instead of counting columns and hoping the count is still right after someone inserts one.

What Do You Use in Excel 2019 or Older?

You use INDEX with MATCH in Excel 2019 or older, because XLOOKUP does not exist there. The closest substitute is =INDEX(C2:C7, MATCH(G2, A2:A7, 0)), which looks up the value in G2 in column A and returns the matching value from column C. The 0 in MATCH asks for an exact match, like XLOOKUP's default, and it can also look to the left, which VLOOKUP cannot.

To add a "not found" message, wrap it in IFERROR: =IFERROR(INDEX(C2:C7, MATCH(G2, A2:A7, 0)), "Not in stock"). If someone opens your workbook in an old version and the XLOOKUP cells show #NAME?, check the formula bar: Excel may show an _xlfn. prefix in front of the function name, which is its way of marking a function the version does not know.

How Do You Fix XLOOKUP Errors?

You fix XLOOKUP errors by reading the error code, because each one points at a different cause. Most XLOOKUP problems are data problems, not formula problems, so check the data before rewriting the formula.

#N/A: no match found

This is the most common result, and it usually means the data does not match exactly even though it looks the same. A trailing space is the usual suspect: "Paint Roller " with a space at the end will not match "Paint Roller", so clean the data with TRIM or use TRIM(G2) as the lookup value. Numbers stored as text are the other classic cause: the number 203 does not match the text "S203" or the text "203", so check the format of the lookup cell and the lookup column and make them the same type.

#VALUE!: ranges are different sizes

Check that the lookup array and the return array are the same size. A2:A7 with D2:D6 is a common slip after copying or editing a formula, and mismatched sizes return #VALUE! instead of a result. Count the rows in both ranges and make them match.

#NAME?: the function is not recognised

Either the function name is misspelled, or the version of Excel you are using does not include XLOOKUP. Check the spelling first, then check the version: if the file was made in Microsoft 365 and you are opening it in Excel 2019 or older, use the INDEX and MATCH substitute from the previous section.

#SPILL!: the spill is blocked

The formula wants to return more than one cell and something is in the way. Click the cell with the error, select the warning icon next to it, and choose Select Obstructing Cells to see what is blocking it. Clear those cells, move the formula, or return a single column instead.

A wrong answer with no error

A wrong answer with no error is the most dangerous outcome, because nothing tells you it happened. Check match_mode first: -1 means "this value or the next smaller one" and 1 means "this value or the next larger one", and mixing them up gives a believable but wrong result. Check search_mode 2 or -2 next: binary search only works on sorted data, and on unsorted data it returns invalid results, so remove that argument unless your data is sorted and very large. Finally, check for duplicates: XLOOKUP returns the first match it finds, or the last with search_mode -1, so if a value appears twice you may be looking at the wrong row.

What Should You Learn After XLOOKUP?

After XLOOKUP, the next step is to make your lookups grow with your data. Ranges like A2:A7 do not grow when you add a new item in row 8, so convert the list to an Excel table: click any cell in the data, press Ctrl + T, confirm the range, and select OK. If the table is named Stock, your formula can then use column names: =XLOOKUP(G2, Stock[SKU], Stock[Price], "Not in stock"). These structured references adjust automatically when you add or remove rows.

The same table skills carry into the rest of your formulas. The percentage increase formula shows how price changes are worked out, and the compound interest formula covers growth over time. And if a supplier sends data as JSON instead of a sheet, the JSON-to-CSV converter turns it into a table you can look up.

Sources

• DataCamp, "XLOOKUP(): Excel's Most Versatile Lookup Function" — six-argument syntax, exact and approximate match, looking left, returning multiple columns, if_not_found, search_mode -1 for the last match, binary search on sorted data only. https://www.datacamp.com/tutorial/xlookup-excel
• SupportYourTech, "How to Do an XLOOKUP in Excel: Steps and Examples" — version gate (Microsoft 365, Excel 2024, Excel 2021, Excel for the web and mobile apps; not in Excel 2019 or 2016), six-argument table, first-formula steps, not-found message, left lookup, whole-record spill, last match, band matching, wildcards, nested XLOOKUP, two-condition match, Excel table structured references, XLOOKUP vs VLOOKUP table, INDEX/MATCH fallback, error fixes. https://www.supportyourtech.com/excel/how-to-do-an-xlookup-in-excel-a-step-by-step-guide-for-beginners/
• How-To Geek, "Forget VLOOKUP in Excel: Here's Why I Use XLOOKUP" — six-argument syntax, VLOOKUP four-argument syntax, exact-match default, worked employee-ID example. https://www.howtogeek.com/forget-vlookup-hlookup-excel-use-xlookup/
• Spreadsheet Center, "XLOOKUP Function: Syntax, Examples & Practice" — match_mode values (0, -1, 1, 2), search_mode values (1, -1, 2, -2), Excel 2021+ requirement, Google Sheets support. https://spreadsheetcenter.com/excel-functions/xlookup/

Frequently Asked Questions

Can XLOOKUP return every match, not just one?

No. XLOOKUP stops at the first match, or the last one with search_mode -1. To list every match, use the FILTER function in Microsoft 365, Excel 2024, or Excel 2021. For example, =FILTER(C2:C8, A2:A8="Rana", "None") spills all of Rana's order amounts into a column.

Is XLOOKUP case-sensitive?

Generally no. In practice XLOOKUP treats "paint roller" and "Paint Roller" as the same value. When capitals matter, such as product codes where "ab12" and "AB12" are different items, use the case-sensitive EXACT function inside it: =XLOOKUP(TRUE, EXACT(B2:B7, G2), A2:A7, "Not found").

Does XLOOKUP work in Google Sheets?

Yes. Google Sheets supports XLOOKUP, so the same six-argument formulas work there, including the if_not_found message and looking to the left. The version gate in this guide applies to Excel: Microsoft 365, Excel 2021, Excel 2024, Excel for the web, and the mobile apps.

Why does my XLOOKUP show #N/A when the value is clearly there?

Usually the data does not match exactly even though it looks the same. A trailing space is the most common cause: "Paint Roller " will not match "Paint Roller", so clean the data with TRIM. The other classic cause is numbers stored as text: the number 203 does not match the text "203", so make the lookup cell and the lookup column the same type.

Can I use XLOOKUP across sheets?

Yes. Put the sheet name and an exclamation mark before each range, for example =XLOOKUP(A2, Prices!A2:A200, Prices!C2:C200, "No price"). The same rule applies: the lookup array and the return array must be the same size.