For years, the great debate in Excel has been VLOOKUP vs. INDEX/MATCH. One was simple but limited; the other was powerful but complex. Then, a new champion entered the ring: XLOOKUP.
If you’ve ever needed to find a piece of data in one table and pull it into another, you’ve used a lookup function. But which one should you be using in 2025? Is it time to finally say goodbye to VLOOKUP?
Quick Answer
- Problem: Choosing the most efficient and reliable lookup function in Excel among
XLOOKUP
,VLOOKUP
, andINDEX/MATCH
for various data retrieval needs.- Modern Formula:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- Legacy Formula:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num])Note:
XLOOKUP
is the modern, versatile choice, often replacingVLOOKUP
andINDEX/MATCH
due to its flexibility and improved error handling.
This is the ultimate showdown. We’ll break down each function, compare them head-to-head, and give you a clear verdict on which one to use to become a more efficient and powerful Excel user.
The Classic Champion: VLOOKUP
VLOOKUP (Vertical Lookup) is the function most Excel users learn first. It’s the workhorse that has powered spreadsheets for decades.
What it does: It searches for a value in the first column of a table and returns a corresponding value from a specified column to the right.
VLOOKUP Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: The value you are looking for.
- table_array: The entire table range where the data resides.
- col_index_num: The number of the column from which to retrieve the value (1 is the first column).
- range_lookup: FALSE for an exact match (most common), TRUE for an approximate match.
VLOOKUP in Action
Let’s say we have this data:
Employee ID [A] | Name [B] | Department [C] |
---|---|---|
101 | John Smith | Sales |
102 | Jane Doe | Marketing |
103 | Peter Jones | Engineering |
To find the department for Employee ID 102, you would use:
=VLOOKUP(102, A2:C4, 3, FALSE)
Result: “Marketing”
Pros & Cons of VLOOKUP
- Pros:
- Simple to learn and widely known.
- Good for straightforward, right-ward lookups.
- Cons:
- Can’t look left: This is its biggest weakness. The lookup value must be in the first column of your table_array.
- Breaks easily: If you insert or delete a column, the col_index_num becomes incorrect, breaking your formula.
- Default is approximate match: Forgetting to add FALSE at the end is a common source of errors.
The Flexible Contender: INDEX/MATCH
For years, INDEX/MATCH was the go-to solution for power users who needed to overcome VLOOKUP’s limitations. It’s not one function, but two working together.
What it does:
- MATCH finds the position (row number) of a value in a list.
- INDEX returns the value at a given position in a list.
Together, they can look up a value in any column and return a corresponding value from any other column.
INDEX/MATCH Syntax
=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))
- return_array: The column from which you want to get the final value.
- lookup_value: The value you are looking for.
- lookup_array: The column that contains your lookup value.
- 0: Specifies an exact match for the MATCH function.
INDEX/MATCH in Action
Using the same data, let’s find the Employee ID for “Jane Doe”:
=INDEX(A2:A4, MATCH("Jane Doe", B2:B4, 0))
Result: 102
How it works:
- MATCH(“Jane Doe”, B2:B4, 0) finds “Jane Doe” in the Name column and returns its position, which is 2.
- INDEX(A2:A4, 2) then returns the value from the 2nd position in the Employee ID column, which is 102.
Pros & Cons of INDEX/MATCH
- Pros:
- Looks left, right, up, or down: Incredibly flexible.
- Resilient: It doesn’t break if you insert or delete columns.
- More efficient on large datasets than VLOOKUP.
- Cons:
- Complex: It requires nesting two different functions, which can be intimidating for beginners.
- Verbose: The formula is longer and can be harder to read.
The Modern Champion: XLOOKUP
Released for Microsoft 365 and Excel 2021, XLOOKUP was designed to be the successor to both VLOOKUP and INDEX/MATCH, combining the power of the latter with the simplicity of the former.
What it does: It looks for a value in one range and returns a corresponding value from another range.
XLOOKUP Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: The value you are looking for.
- lookup_array: The column or row to search in.
- return_array: The column or row to return a value from.
- [if_not_found]: (Optional) What to return if no match is found (no more #N/A errors!).
- [match_mode]: (Optional) 0 for exact match (default), or other options for approximate matches.
- [search_mode]: (Optional) Specify search direction (e.g., first-to-last or last-to-first).
XLOOKUP in Action
Let’s find the Employee ID for “Jane Doe” again:
=XLOOKUP("Jane Doe", B2:B4, A2:A4)
Result: 102
It’s as simple as VLOOKUP but as powerful as INDEX/MATCH.
Pros & Cons of XLOOKUP
- Pros:
- Simple and intuitive syntax.
- Can look left or right without changing the formula structure.
- Defaults to an exact match, which is safer and what users want most of the time.
- Built-in if_not_found argument eliminates the need for IFERROR.
- Can return entire rows or columns, not just a single value.
- Can search from bottom-to-top to find the last occurrence of a value.
- Cons:
- Availability: Only available in Microsoft 365, Excel 2021, and Excel for the web. If you need to share workbooks with users on older versions (like Excel 2016 or 2019), they won’t be able to use it.
Head-to-Head Comparison: Features & Performance
Let’s pit these three giants against each other across key criteria:
Feature/Aspect | XLOOKUP | VLOOKUP | INDEX/MATCH |
---|---|---|---|
Lookup Direction | Bi-directional (left or right) | Right-only (lookup column must be first) | Bi-directional (flexible) |
Exact Match (Default) | Yes | No (requires FALSE or 0) |
Yes (requires 0 ) |
Approximate Match | Yes (with match_mode -1 or 1) |
Yes (with TRUE or omitted) |
Yes (with match_type 1 or -1) |
Error Handling (#N/A ) |
Built-in if_not_found argument |
Returns #N/A by default |
Returns #N/A by default |
Column Insertion/Deletion | Robust (arrays shift automatically) | Breaks easily (col_index_num becomes wrong) |
Robust (arrays shift automatically) |
Return Multiple Values | Yes (returns an array that spills) | No | No |
Two-Way Lookup | Single formula | No (requires complex nesting) | Yes (nested MATCH or MATCH /MATCH ) |
Multiple Criteria | Yes (with & or TRUE ) |
No (requires helper column or complex array formulas) | Yes (with array formulas or helper column) |
Performance | Generally fast for large datasets | Slower, especially with exact matches on large data | Generally faster than VLOOKUP for large datasets |
Readability | Very good, arguments are clear | Good for simple lookups, confusing col_index_num |
Can be intimidating for beginners |
Wildcard Characters | Yes (with match_mode 2) |
Yes (with range_lookup FALSE) |
Yes (with match_type 0) |
Readability & Ease of Use
XLOOKUP
: Arguably the most intuitive. Its arguments clearly definelookup_array
andreturn_array
, eliminating thecol_index_num
guesswork.VLOOKUP
: Simple for basic right-side lookups, but thecol_index_num
can be a pain, especially if columns are reordered.INDEX/MATCH
: Can be intimidating at first due to nesting two functions. However, once understood, its logic is very powerful.
Lookup Direction (Left vs. Right)
This is a critical distinction. VLOOKUP
is famously restricted to looking up values only to the right of the lookup column. XLOOKUP
and INDEX/MATCH
have no such limitation; they can look up values anywhere.
Exact vs. Approximate Match
XLOOKUP
defaults to an exact match, which is what most users need most of the time. VLOOKUP
and INDEX/MATCH
require you to explicitly specify an exact match (by using FALSE
or 0
respectively), otherwise, they might return an approximate match, leading to incorrect results if your data isn’t perfectly sorted.
Error Handling
XLOOKUP
has a built-in [if_not_found]
argument, making it easy to display a custom message (e.g., “Not Found”) instead of the unsightly #N/A
error. With VLOOKUP
or INDEX/MATCH
, you typically need to wrap them in an IFERROR
function.
Insert/Delete Columns
This is where VLOOKUP
often breaks. If you insert or delete a column within the table_array
, the col_index_num
in your VLOOKUP
formula will become incorrect, leading to #REF!
errors or wrong results. XLOOKUP
and INDEX/MATCH
are immune to this, as they refer to distinct ranges, not column numbers.
Return Multiple Values (Spill)
XLOOKUP
(in modern Excel versions with Dynamic Arrays) can return multiple values if the return_array
has more than one column or if there are multiple matches and you use search_mode
0 or 2 (which is not standard for returning multiple matches explicitly, but rather if the lookup_value
itself could return multiple return_array
values due to search_mode
in conjunction with match_mode
). More commonly, you’d use XLOOKUP
with a range in return_array
and it will spill.
Two-Way Lookup
This involves looking up a value based on both a row and a column criterion. XLOOKUP
can handle this in a single, elegant formula. INDEX/MATCH
can do this with a nested MATCH
for both row and column. VLOOKUP
struggles significantly and requires very complex nesting or helper cells.
Multiple Criteria Lookup
Finding a value based on two or more conditions (e.g., “Sales for Product A in Region East”). XLOOKUP
can achieve this by concatenating criteria or using a TRUE
array. INDEX/MATCH
can also do this with array formulas. VLOOKUP
cannot directly handle multiple criteria without a helper column.
Performance Considerations
For extremely large datasets (tens or hundreds of thousands of rows), INDEX/MATCH
is generally recognized as being more efficient than VLOOKUP
, especially for exact matches, because VLOOKUP
processes the entire table_array
. XLOOKUP
is highly optimized and generally outperforms both, particularly for larger datasets, due to its efficient search algorithms.
Common Pitfalls & Fixes
Even the best functions can lead to errors. Here are some common issues and how to resolve them:
#N/A Errors
This error means “Not Available” or “No Match Found.”
VLOOKUP
/INDEX/MATCH
: Occurs when thelookup_value
isn’t found.- Fix: Double-check spelling, leading/trailing spaces (use
TRIM
), or data types (number vs. text). Ensure yourlookup_array
or first column oftable_array
actually contains the value.
- Fix: Double-check spelling, leading/trailing spaces (use
XLOOKUP
: Also occurs if no match is found, unless you’ve used the[if_not_found]
argument.- Fix: Use the
[if_not_found]
argument to provide a user-friendly message, e.g.,=XLOOKUP(A2, B:B, C:C, "Product Not Listed")
.
- Fix: Use the
#REF! Errors
The #REF!
error indicates an invalid cell reference.
VLOOKUP
: Most common here. If you insert or delete columns within thetable_array
after writing the formula, yourcol_index_num
becomes incorrect.- Fix: Update the
col_index_num
manually or, better yet, switch toXLOOKUP
orINDEX/MATCH
.
- Fix: Update the
Performance Issues with Large Datasets
Extensive use of VLOOKUP
(especially with approximate matches on unsorted data or exact matches on very large datasets) can slow down your spreadsheet.
- Fix: For large datasets, prefer
XLOOKUP
orINDEX/MATCH
. ForVLOOKUP
, ensure therange_lookup
isFALSE
(exact match) where possible, and avoid using entire columns as references (e.g.,A:A
instead ofA2:A1000
).
Incorrect Match Types
Using an approximate match when an exact match is needed, or vice-versa.
- Fix: Always verify the
[range_lookup]
argument forVLOOKUP
and[match_type]
forMATCH
/XLOOKUP
. Defaulting to exact match (FALSE
forVLOOKUP
,0
forMATCH
/XLOOKUP
or omittingmatch_mode
forXLOOKUP
) is generally safest unless you explicitly intend an approximate match on sorted data.
The Verdict (2025)
For modern Excel users, XLOOKUP is the undisputed champion.
It is simpler, safer, more flexible, and more powerful than both of its predecessors. If you have Microsoft 365 or Excel 2021, you should make XLOOKUP your default lookup function.
So, when should you use the others?
- Use VLOOKUP when: You are working in an environment where you must maintain compatibility with very old versions of Excel (pre-2021) and your lookup is a simple, right-ward one.
- Use INDEX/MATCH when: You need to perform complex lookups (like looking left) and must maintain compatibility with Excel 2010, 2013, 2016, or 2019.
For everyone else, it’s time to embrace the future. Learning and mastering XLOOKUP is one of the single best things you can do to improve your Excel skills in 2025.
Download Example Practice File
To follow along and try these formulas yourself, download our free practice file. The file contains the sample data and all the formulas discussed in this article.
FAQ
What is the biggest advantage of XLOOKUP over VLOOKUP?
The biggest advantage of XLOOKUP
is its flexibility and robustness. Unlike VLOOKUP
, XLOOKUP
can look up values to the left or right of the lookup column, doesn’t break when columns are inserted or deleted, and has a built-in if_not_found
argument for cleaner error handling. It’s also easier to perform exact matches, as it’s the default.
Can XLOOKUP truly replace both VLOOKUP and INDEX/MATCH?
Yes, XLOOKUP
is designed to effectively replace both VLOOKUP
and INDEX/MATCH
for most common lookup scenarios. It combines the ease of use of VLOOKUP
with the flexibility and robustness of INDEX/MATCH
, often in a more concise and readable syntax. While some highly specialized INDEX/MATCH
array formulas might still have niche uses, XLOOKUP
covers the vast majority of lookup needs.
Is INDEX/MATCH faster than VLOOKUP for large datasets?
Generally, yes, for exact matches, INDEX/MATCH
tends to be more efficient than VLOOKUP
on very large datasets. This is because VLOOKUP
often processes the entire table_array
even when it only needs to look in the first column, whereas INDEX/MATCH
works with specific lookup and return arrays, which can be more optimized. However, XLOOKUP
often outperforms both due to its internal optimizations.
Why does my VLOOKUP return a wrong value even with FALSE for exact match?
If VLOOKUP
returns an incorrect value even with FALSE
(exact match), check for: 1) Typos or extra spaces in your lookup_value
or the lookup column. Use the TRIM
function to remove leading/trailing spaces. 2) Data type mismatches, such as looking up a number stored as text or vice versa. Ensure both the lookup_value
and the lookup column data are of the same type.
When should I still use VLOOKUP?
You should still use VLOOKUP
if you are working in older versions of Excel (pre-Excel 2021 or Microsoft 365) where XLOOKUP
is unavailable, or if you are sharing your workbook with users who only have older Excel versions. For simple, right-directional lookups, VLOOKUP
remains a familiar and functional tool in these environments.
Related Formulas
HLOOKUP — While VLOOKUP
looks up values vertically (in columns), HLOOKUP
looks up values horizontally (in rows). It searches for a value in the first row of a table and returns a value from a specified row in the same column.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
SUMIFS — This function is used to sum values in a range that meet multiple criteria. While not a direct lookup, it’s often used when you need to sum data associated with specific conditions, performing a type of aggregated lookup.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
LOOKUP — An older, simpler lookup function that can handle both vector (single row/column) and array (multiple rows/columns) forms. It’s often used for approximate matches on sorted data and can mimic some behaviors of XLOOKUP
or VLOOKUP
in specific contexts.
=LOOKUP(lookup_value, lookup_vector, [result_vector])
