The core Excel formula for lookup and match is =INDEX(range, MATCH(lookup_value, lookup_array, 0)), which returns a value at the intersection of a row and column. For a simpler two-column lookup, use =VLOOKUP(lookup_value, table_array, column_index, FALSE), or =XLOOKUP(lookup_value, lookup_array, return_array) if you have Excel 2021 or Microsoft 365.
Why You Need More Than One Lookup Formula
Excel offers several lookup functions because data isn’t always arranged the same way. VLOOKUP searches only the first column of a table and returns a value to the right. HLOOKUP searches the first row and returns a value below. INDEX and MATCH work together to search any column or row and return a value from any other column or row, without the left-to-right restriction. XLOOKUP, introduced in 2020, replaces both VLOOKUP and HLOOKUP with a single function that handles left and right lookups, approximate matches, and missing values.
Knowing which to use depends on your Excel version, the layout of your data, and whether you need an exact or approximate match. The rest of this article explains each method with concrete examples and common pitfalls.
How VLOOKUP Works (and Its Limits)
VLOOKUP stands for “vertical lookup.” It searches for a value in the leftmost column of a table and returns a value in the same row from a column you specify. The syntax is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example: with product IDs in A2:A10 and prices in B2:B10, to find the price for ID “P103”:
=VLOOKUP("P103", A2:B10, 2, FALSE)
This returns the price from column B. The FALSE argument forces an exact match. If you use TRUE or omit it, Excel assumes your data is sorted and returns an approximate match, which can give wrong results if the data isn’t sorted.
Limitations of VLOOKUP:
- It can only look to the right. If your return column is to the left of the lookup column, VLOOKUP fails.
- It doesn’t handle columns being inserted or deleted well—the column index number becomes stale.
- It returns the first match only; if there are duplicates, you can’t easily get the second or third.
- It’s slower on large datasets compared to INDEX/MATCH or XLOOKUP.
Using INDEX and MATCH for Flexible Lookups
INDEX returns a value at a given row and column position in a range. MATCH returns the position of a value in a row or column. Combining them gives you a lookup that works in any direction.
Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Example: with employee names in A2:A20 and department codes in C2:C20, to find the department for “Jane Smith”:
=INDEX(C2:C20, MATCH("Jane Smith", A2:A20, 0))
Here, MATCH returns the row number where “Jane Smith” appears in A2:A20, and INDEX returns the corresponding value from C2:C20. The 0 in MATCH means exact match.
For a two-way lookup (find a value at the intersection of a specific row and column), use two MATCH functions:
=INDEX(B2:E10, MATCH("ProductX", A2:A10, 0), MATCH("Q2", B1:E1, 0))
This returns the value where the row matches “ProductX” and the column matches “Q2”. This is a common pattern for sales tables or grade matrices.
XLOOKUP: The Modern Replacement
XLOOKUP is available in Excel 2021, Excel for Microsoft 365, and Excel for the web. It simplifies everything. Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Example: with product names in A2:A10 and stock quantities in C2:C10:
=XLOOKUP("Widget", A2:A10, C2:C10)
This returns the stock quantity for “Widget”. Unlike VLOOKUP, the return array can be to the left or right of the lookup array. You can also specify a custom message if not found:
=XLOOKUP("Gadget", A2:A10, C2:C10, "Not found")
XLOOKUP defaults to exact match, which is what most users want. It also supports approximate matches (for ranges like tax brackets) by setting the match_mode argument to -1 (exact or next smaller) or 1 (exact or next larger).
One caveat: if you share a workbook with someone using an older Excel version, XLOOKUP will show a #NAME? error. In that case, use INDEX/MATCH or VLOOKUP for compatibility.
How to Choose the Right Formula
Follow these simple rules:
- Use XLOOKUP if you have Excel 2021 or Microsoft 365 and need a simple, flexible lookup.
- Use INDEX/MATCH if you need to look to the left, or if you’re working in an older Excel version (2016 or earlier) and want better performance on large datasets.
- Use VLOOKUP only if your data is simple, you only need to look to the right, and you’re confident the column order won’t change.
- Use HLOOKUP only when your data is organized horizontally (lookup values in the first row).
For approximate matches (e.g., finding a tax rate based on income brackets), both VLOOKUP and XLOOKUP can handle it, but you must sort your data in ascending order. For exact matches, always use FALSE or 0 to avoid accidental approximate matches.
Common Problems and How to Fix Them
Here are the most frequent lookup errors and their solutions:
- #N/A error: The lookup value doesn’t exist exactly. Check for extra spaces, different data types (text vs. number), or non-printable characters. Use
=TRIM(A2)to clean data. - #VALUE! error: The column index in VLOOKUP is greater than the number of columns in the table, or the range isn’t aligned. Double-check the table_array.
- #REF! error: The column index refers to a column outside the table. This often happens after deleting a column.
- Wrong results with VLOOKUP TRUE: If you use
TRUEwithout sorting, you get unpredictable results. Always sort the lookup column ascending, or useFALSE. - Lookup value is a number stored as text: Use
=VLOOKUP(VALUE(A2), ...)or convert the column to numbers. - Duplicate lookup values: All these formulas return the first match. To get the last match, use
=XLOOKUP(lookup_value, lookup_array, return_array, , , -1)(search from last to first).
Performance and Best Practices
On large datasets (tens of thousands of rows), VLOOKUP can slow down your workbook because it scans the entire table. INDEX/MATCH is faster because MATCH only scans one column. XLOOKUP is also optimized and generally fast, but it can be slower than INDEX/MATCH on very large datasets if you use multiple XLOOKUPs.
To speed up lookups:
- Use exact match (
FALSEor0) whenever possible. - Sort your data if you need approximate match—this allows Excel to use a binary search.
- Limit the lookup range to the exact rows needed, not entire columns (e.g., A2:A1000 instead of A:A).
- Use Excel Tables (Insert > Table) so ranges expand automatically when you add data.
- Consider using Power Query for complex merges instead of formulas, especially if you need to refresh data regularly.
Frequently Asked Questions
What is the difference between VLOOKUP and INDEX/MATCH?
VLOOKUP can only return a value from a column to the right of the lookup column. INDEX/MATCH can return a value from any column, including those to the left, and is generally faster on large datasets. INDEX/MATCH also doesn’t break if you insert or delete columns in the table, because you reference the specific return range rather than a column number.
Can I use a lookup to return multiple matches?
Yes, but not with a single VLOOKUP or XLOOKUP. Use the FILTER function (Excel 2021/Microsoft 365) to return all matching rows: =FILTER(A2:C20, A2:A20="ProductX"). For older versions, you’d need an array formula with INDEX, SMALL, and IF, which is more complex.
Why does my VLOOKUP return #N/A even though the value exists?
Most likely a data type mismatch. The lookup value might be a number stored as text, or vice versa. Check with =ISNUMBER(A2) and =ISTEXT(A2). Also check for trailing spaces using =LEN(A2) and compare to the length of the lookup cell. Use =TRIM() to remove extra spaces.
Is XLOOKUP available in all Excel versions?
No. XLOOKUP is available in Excel 2021, Excel for Microsoft 365, and Excel for the web. It is not in Excel 2019 or earlier. If you send a workbook with XLOOKUP to someone on an older version, they’ll see #NAME? errors. Use INDEX/MATCH for maximum compatibility.
How do I do a case-sensitive lookup?
VLOOKUP, INDEX/MATCH, and XLOOKUP are not case-sensitive by default. To make a lookup case-sensitive, use the EXACT function with an array formula. For example, in Excel 365: =INDEX(B2:B10, MATCH(TRUE, EXACT(A2:A10, "abc"), 0)) entered as a dynamic array formula (just press Enter in 365). For older versions, you’d need to press Ctrl+Shift+Enter.
Can I use a lookup formula to return a value from another sheet?
Yes. Simply reference the other sheet in the range. For example: =VLOOKUP(A2, Sheet2!A:B, 2, FALSE) or =XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B). Make sure the sheet name has no spaces, or enclose it in single quotes: ='My Sheet'!A:B.