If you have ever hit a wall with VLOOKUP — needing to look left, working around broken formulas after a column was inserted, or searching across multiple conditions — INDEX MATCH in Excel is the solution. This combination of two functions is the most flexible lookup tool in Excel, works in every version from Excel 2003 to Microsoft 365, and once learned, tends to replace VLOOKUP entirely. In this complete guide, you will learn exactly how each function works, how they combine, and see six practical examples that cover every scenario you are likely to encounter.
How INDEX and MATCH Work Individually
Before combining them, it helps to understand what each function does on its own.
Returns a value from a range at a specific row and/or column position.
array— the range to return fromrow_num— the row position to retrieve[col_num]— optional column position
Think of it as: "Give me what is in row 3 of this column."
Returns a position number — the row or column where a value is found.
lookup_value— what you are searching forlookup_array— where to searchmatch_type— use 0 for exact match
Think of it as: "Tell me which row 'London' is in."
The power comes when you nest MATCH inside INDEX: MATCH finds which row the value is in, and INDEX retrieves what is in that row from a completely separate column.
0 at the end of MATCH. The match_type argument defaults to 1 (approximate match), which requires your data to be sorted and will silently return wrong values if it is not. Always type the 0 explicitly.
INDEX MATCH vs VLOOKUP — Key Differences
- Cannot look left — return column must be to the right of the lookup column
- Breaks when columns are inserted or deleted (column index number changes)
- Slower on large datasets — scans entire table array
- Cannot do two-way lookups without workarounds
- Looks in any direction — left, right, up, down
- Column-insertion proof — ranges are independent
- Faster on large datasets — only scans the lookup column
- Supports two-way, multiple-criteria, and case-sensitive lookups natively
Example 1: Basic Lookup (The Everyday Pattern)
This is the equivalent of VLOOKUP — find a value in one column and return a corresponding value from another column. Here you look up a product name and return its price.
$ signs on the return_range and lookup_range so the formula stays correct when copied to other rows. Only the lookup_value cell (E1) should be relative.
Example 2: Left Lookup (What VLOOKUP Cannot Do)
VLOOKUP can only return values from columns to the right of the lookup column. INDEX MATCH has no such restriction — the return range and lookup range are completely independent, so you can look left, right, or across any columns.
Example 3: Two-Way Lookup (Row AND Column)
INDEX MATCH MATCH is the two-dimensional version — one MATCH finds the row, a second MATCH finds the column. This is perfect for matrix lookups like monthly sales by region, or pricing tables with multiple tiers.
$B$2:$D$4 excludes the headers. The row MATCH looks in $A$2:$A$4 (region names). The column MATCH looks in $B$1:$D$1 (month headers). Both must align exactly with the data range dimensions.
Example 4: Multiple Criteria Lookup
Standard lookups match on one column. INDEX MATCH can match on two or more columns simultaneously using boolean logic inside MATCH — perfect for looking up a record that is uniquely identified by a combination of values, like a name AND a department.
A=criteria) creates an array of TRUE/FALSE values. Multiplying them converts TRUE to 1 and FALSE to 0. Only rows where ALL conditions are TRUE produce a 1. MATCH looks for the first 1 in that resulting array.
Example 5: Partial Match with Wildcards
MATCH supports the same wildcard characters as COUNTIFS and SUMIFS: * for any sequence and ? for a single character. This is useful when your lookup value might be a partial company name, a truncated code, or part of a product SKU.
Example 6: Case-Sensitive Lookup
Every lookup function in Excel is case-insensitive by default — "apple", "APPLE", and "Apple" all match equally. When your data contains mixed-case values that must be distinguished (such as product codes like "ABC-001" vs "abc-001"), pair INDEX with EXACT inside an array MATCH.
EXACT(E1, $A$2:$A$4) compares E1 against every cell in column A and returns an array like {FALSE; TRUE; FALSE}. MATCH finds the first TRUE (position 2), and INDEX returns the value at that row.
Quick-Reference: All INDEX MATCH Patterns
| Use case | Formula pattern |
|---|---|
| Basic lookup (right) | =INDEX(B:B, MATCH(val, A:A, 0)) |
| Left lookup | =INDEX(A:A, MATCH(val, B:B, 0)) |
| Two-way (row + column) | =INDEX(data, MATCH(r,rows,0), MATCH(c,cols,0)) |
| Multiple criteria (AND) | =INDEX(C:C, MATCH(1,(A:A=v1)*(B:B=v2),0)) |
| Partial match (starts with) | =INDEX(B:B, MATCH(val&"*", A:A, 0)) |
| Case-sensitive match | =INDEX(B:B, MATCH(TRUE, EXACT(val,A:A), 0)) |
| With IFERROR (hide #N/A) | =IFERROR(INDEX(B:B, MATCH(val,A:A,0)), "") |
Troubleshooting: Why Is INDEX MATCH Returning a Wrong Value or #N/A?
1. Missing 0 in MATCH — the most common mistake
If you write =MATCH(val, range) without the third argument, Excel defaults to approximate match (match_type 1), which requires sorted data and silently returns wrong values on unsorted lists. Always type =MATCH(val, range, 0).
2. Return range and lookup range are different sizes
The return_range (first argument of INDEX) and the lookup_range (second argument of MATCH) must contain the same number of rows. If MATCH finds position 5 but INDEX's range only has 4 rows, you get a #REF! error.
3. Data type mismatch
If the lookup value is a number but the lookup range contains text-formatted numbers (or vice versa), MATCH returns #N/A. Add TEXT(val,"0") or VALUE(val) to force matching data types.
4. Extra spaces in cell values
A cell containing "London " (trailing space) will not match "London". Use TRIM() on your data, or use a wildcard: MATCH(val&"*", range, 0).
5. Multiple criteria formula needs Ctrl+Shift+Enter in older Excel
Boolean multiplication creates an array formula. In Excel 2019 and older, you must press Ctrl+Shift+Enter instead of just Enter. Curly braces { } will appear around the formula to confirm it is entered as an array. In Excel 365 and 2021, regular Enter works.
=MATCH(val, range, 0). If MATCH returns a number, the lookup is working and the issue is in your INDEX range. If MATCH returns #N/A, the value genuinely does not exist in the lookup range.
Frequently Asked Questions
-
What does INDEX MATCH do in Excel?INDEX MATCH combines two functions: MATCH finds the row (or column) position of a lookup value, and INDEX returns the value at that position in a separate range. Together they perform a flexible lookup that can retrieve data in any direction — left, right, up, or down — without the limitations of VLOOKUP.
-
Why use INDEX MATCH instead of VLOOKUP?INDEX MATCH has four key advantages: it can look left (VLOOKUP cannot), it does not break when columns are inserted or deleted, it performs faster on large datasets, and it supports two-way and multiple-criteria lookups without workarounds. For any moderately complex spreadsheet, INDEX MATCH is the more reliable choice.
-
How do I use INDEX MATCH with multiple criteria?Multiply conditions inside MATCH using boolean logic:
=INDEX(return_range, MATCH(1, (range1=crit1)*(range2=crit2), 0)). In Excel 365 this works as a regular formula. In Excel 2019 and older, enter it with Ctrl+Shift+Enter. Each condition in the multiplication must be true for a row to be matched. -
What is the difference between INDEX MATCH and XLOOKUP?XLOOKUP (Excel 365 and 2021 only) is simpler to write and handles most INDEX MATCH scenarios in fewer arguments. However, INDEX MATCH works in all Excel versions from 2003 onwards, supports two-way lookups natively with INDEX MATCH MATCH, and is preferred by many advanced users for its consistency and deeper flexibility with array formulas.
-
Why is my INDEX MATCH returning the wrong value?The most common cause is forgetting to set match_type to 0 (exact match) in MATCH — it defaults to 1 (approximate match), which requires sorted data and returns wrong results on unsorted lists. Other causes include mismatched range sizes, data type mismatches, or extra spaces in cell values.
-
Can INDEX MATCH look up to the left?Yes — this is one of its biggest advantages over VLOOKUP. Because the return range and lookup range in INDEX MATCH are completely independent arguments, you can return a value from any column regardless of its position relative to the lookup column — left, right, or even from a different sheet.