XLOOKUP vs VLOOKUP vs INDEX MATCH: Which One to Use on Your Exports
XLOOKUP, VLOOKUP and INDEX MATCH compared on two exports: versions, left lookups, two criteria, inserted columns, and how to check no row was lost.
- Excel
- Formulas
- Reporting Workflows

On this page
- What is the difference between XLOOKUP and VLOOKUP?
- Which lookup works on my exports?
- How do you write the same lookup with VLOOKUP, INDEX MATCH and XLOOKUP?
- How do you return a column to the left of the ID?
- What happens when someone inserts a column?
- How do you look up with two criteria?
- What else can XLOOKUP do that VLOOKUP can't?
- How do you check that the lookup lost no rows?
- Does Google Sheets have XLOOKUP?
- Where does Glow Reports fit?
- Common questions
You have an order export and a customer export, and the report needs each order's country or plan. Excel offers three ways to join them: VLOOKUP, INDEX with MATCH, and XLOOKUP. Use XLOOKUP if everyone who opens the file has Excel 2021 or later, or Microsoft 365. Use INDEX MATCH if someone is still on Excel 2016 or 2019. Keep VLOOKUP for old files, and always end it with FALSE. This guide compares the three on the same two exports, then shows how to check that the join lost no rows.
What is the difference between XLOOKUP and VLOOKUP?
XLOOKUP searches one column and returns another column you point at, on either side, with an exact match by default. VLOOKUP searches only the first column of a range, returns a column counted by number to its right, and defaults to an approximate match. INDEX MATCH sits between the two and works in every version.
| Point | VLOOKUP | INDEX MATCH | XLOOKUP |
|---|---|---|---|
| Excel versions | All | All | Microsoft 365, 2021, 2024 |
| Default match | Approximate | Approximate (MATCH type 1) | Exact |
| Return a column on the left | No | Yes | Yes |
| Inserted column breaks it | Yes, the column number shifts | No | No |
| Value when nothing is found | #N/A, wrap it in IFNA | #N/A, wrap it in IFNA | Fourth argument, such as "Not found" |
| Search from the last row | No | No | Yes, search mode -1 |
| Return several columns at once | No | No | Yes, the result spills |
| Google Sheets | Yes | Yes | Yes |
Microsoft's own VLOOKUP page now opens with a tip to try XLOOKUP, which "works in any direction and returns exact matches by default" Microsoft Support. The XLOOKUP page states that it isn't available in Excel 2016 and Excel 2019 Microsoft Support.
Which lookup works on my exports?
All three work when the ID sits to the left of the value you need. When the value sits to the left of the ID, only INDEX MATCH and XLOOKUP work. The customer export below puts the name before the ID, as some CRM exports do.
Type the two sample exports on sheets named Orders and Customers, headers in row 1, to follow along.
Customers sheet, columns A to D:
| Name | Customer ID | Country | Plan |
|---|---|---|---|
| Acme Ltd | C-104 | France | Pro |
| Brightway | C-101 | UK | Starter |
| Corvel | C-103 | France | Starter |
| Dunmore | C-102 | US | Pro |
Orders sheet, columns A to C:
| Order | Customer ID | Amount |
|---|---|---|
| O-1 | C-101 | 120 |
| O-2 | C-104 | 340 |
| O-3 | C-102 | 80 |
| O-4 | C-105 | 60 |
| O-5 | C-104 | 210 |
| O-6 | C-103 | 95 |
C-105 has no row in the customer export. That happens in real files, for example when a customer was deleted from the CRM after ordering. A good lookup shows it instead of hiding it.
How do you write the same lookup with VLOOKUP, INDEX MATCH and XLOOKUP?
Each formula below goes in D2 of the Orders sheet and returns the customer's country, France for O-2. The VLOOKUP range must start at the Customer ID column, the INDEX MATCH pair names the return column and the search column, and XLOOKUP takes the search column, the return column and a not-found value.
=VLOOKUP(B2,Customers!$B$2:$D$5,2,FALSE)=INDEX(Customers!$C$2:$C$5,MATCH(B2,Customers!$B$2:$B$5,0))=XLOOKUP(B2,Customers!$B$2:$B$5,Customers!$C$2:$C$5,"Not found")For O-4, the first two return #N/A and XLOOKUP returns Not found. The VLOOKUP needs FALSE as its last argument. Without it, VLOOKUP assumes the first column is sorted and returns the closest value Microsoft Support. On an unsorted ID column, that can give a wrong country with no error at all.
MATCH has the same trap. Its match type defaults to 1, which finds the largest value less than or equal to the one you look for, in a list sorted ascending. Type 0 finds the exact value in any order Microsoft Support. Always write the 0.
How do you return a column to the left of the ID?
Use INDEX MATCH or XLOOKUP. VLOOKUP can only look from left to right, so the value you search for must sit to the left of the value you want back. Microsoft's own advice for a sheet built the other way is to use INDEX and MATCH instead Microsoft Support.
In the sample, the customer name is in column A, left of the ID in column B. These two formulas return Acme Ltd for O-2:
The usual VLOOKUP workaround is to move or copy the ID column to the left in the customer sheet. That works until next month's export arrives in the original layout and someone forgets to move it again.
What happens when someone inserts a column?
VLOOKUP keeps asking for the same column number, so it returns the wrong column without an error. INDEX MATCH and XLOOKUP point at the return column itself, and Excel adjusts that reference when columns move.
In the sample, returns the country, the second column of the range. Insert an Email column between Customer ID and Country, and the range grows to B:E. The formula still asks for column 2, which is now Email. The report fills with addresses where countries should be, and nothing flags it. It is a frequent reason a VLOOKUP report "breaks on its own" after an export changes layout.
How do you look up with two criteria?
With XLOOKUP in Microsoft 365, multiply two tests and look for 1: . Each test returns TRUE or FALSE for every row, the product is 1 only where both are true, and XLOOKUP returns the first such row.
A price list by product and month is the typical case: product in A, month in B, price in C, and the product and month you want in F2 and G2. In older versions, and with VLOOKUP, add a helper column that joins the two keys, such as , and look up in it. The separator stops "AB" plus "1" from matching "A" plus "B1".
If you need a total rather than one value, such as all orders for one customer in one month, you don't need a lookup at all. SUMIFS adds every matching row, and SUMIFS with multiple criteria covers the date-range cases.
What else can XLOOKUP do that VLOOKUP can't?
Three things matter for reports: it can return several columns at once, search from the last row, and return a value you choose when nothing matches. Each one replaces a helper column or a nested formula you would otherwise need.
- Several columns: returns country and plan side by side. Microsoft's examples show one XLOOKUP returning both an employee's name and department Microsoft Support.
- Last match: the sixth argument set to -1 searches from the last item. With orders sorted by date, returns 210, the amount of the customer's latest order, O-5. VLOOKUP and MATCH return the first match only.
- Not found: the fourth argument replaces #N/A. With VLOOKUP, wrap the formula in IFNA instead, as shown in VLOOKUP not working.
XLOOKUP also accepts wildcard matching with match mode 2, and approximate matches to the next smaller or larger item with -1 and 1, which suits tax bands or price tiers.
How do you check that the lookup lost no rows?
Count the rows that found nothing, check for duplicate IDs in the lookup table, and compare totals before and after. Three formulas are enough, and they catch the errors that a lookup never reports by itself.
- Rows with no match: with XLOOKUP, or with VLOOKUP or INDEX MATCH. The sample returns 1, for O-4.
- Duplicate IDs in the customer export: next to each ID. Every lookup here returns the first match, so a customer listed twice gets the country of whichever row comes first. Anything above 1 needs a look.
- Totals: add the amounts by country with SUMIFS and compare the sum with . In the sample, France 645, UK 120, US 80 and Not found 60 add up to 905, the total of the export. A gap means rows fell out somewhere.
The compare two columns guide shows how to list the missing IDs themselves, so you can fix them at the source.
Does Google Sheets have XLOOKUP?
Yes. Google added XLOOKUP and XMATCH to Sheets in August 2022 Google Workspace Updates. The arguments match Excel's: search key, lookup range, result range, a missing value, a match mode and a search mode Google Docs Editors Help.
Google's own page shows how XLOOKUP replaces VLOOKUP with FALSE, and how it returns a column to the left of the search column, which VLOOKUP can only do with an array trick. VLOOKUP and INDEX MATCH work in Sheets too, with the same defaults as in Excel.
Where does Glow Reports fit?
Lookups are how a spreadsheet joins two exports. Each new month means pasting the files again, checking the IDs, and counting what didn't match. The checks above are the part people skip, and the part that keeps the report right.
With Glow Reports, you upload both CSV or Excel files and ask for the report in plain language, such as revenue by country. The AI writes the SQL, including the join between the two files, builds the charts and drafts the text. The SQL sits under each figure, so you can see which column joins the files and how unmatched rows are handled, and edit it if needed. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.
Common questions
Is XLOOKUP better than VLOOKUP?
For new work in Excel 2021, 2024 or Microsoft 365, yes. It matches exactly by default, returns columns on either side, survives inserted columns and takes a not-found value. If the file goes to someone on Excel 2016 or 2019, use INDEX MATCH, which works everywhere.
Is INDEX MATCH still worth learning?
Yes, as long as some people open your files in Excel 2016 or 2019, where XLOOKUP isn't available. It also teaches how lookups work: MATCH finds a position, INDEX returns what sits at that position.
Why does XLOOKUP return #N/A when the value is there?
The usual causes are the same as for VLOOKUP: an ID stored as text in one file and as a number in the other, or a trailing space. Check with and on both sides, then clean the column as in how to clean data in Excel.
Can XLOOKUP return every match?
No. It returns one row per lookup. To list every order of a customer, use FILTER in Microsoft 365, or a PivotTable. To total them, use SUMIFS.
What is XMATCH?
XMATCH is the new MATCH. It returns the position of a value, with an exact match by default and a search from the last item if you ask for it. Pair it with INDEX the same way as MATCH Microsoft Support.
I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.
More from El Mehdi BenaneUpload your order and customer exports to Glow Reports and ask for revenue by country, with the SQL join behind each figure.


