INDEX MATCH combines Excel or Google Sheets’ INDEX and MATCH functions to look up a value and return a related result, even when the return column is left of the lookup column.
These examples start with a basic exact match, then show how to handle two-way searches, multiple criteria, and common edge cases.
How INDEX MATCH works
MATCH finds a position in a range; INDEX returns the value at a position in another range.
The basic pattern is:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
The final 0 tells MATCH to search for an exact match.
The lookup range and return range should cover corresponding rows.
For example, if a name is found in row 4 of the lookup range, INDEX returns the value from row 4 of the return range.
Basic exact-match example
Suppose a worksheet has employee names in A2:A5 and departments in B2:B5:
A2: Ana —B2: SalesA3: Ben —B3: FinanceA4: Cara —B4: MarketingA5: Dev —B5: Operations
To return the department for the name entered in D2, use:
=INDEX(B2:B5, MATCH(D2, A2:A5, 0))
If D2 contains Cara, the result is Marketing.
MATCH finds Cara’s position in A2:A5; INDEX returns the value at the same position in B2:B5.
Look up a value to the left
VLOOKUP typically searches the first column of its table and returns a value from a column to its right.
INDEX MATCH can search in one column and return from another regardless of their left-to-right order.
With product names in B2:B5 and product codes in A2:A5, find the code for the product in D2:
=INDEX(A2:A5, MATCH(D2, B2:B5, 0))
If D2 contains a product name in the list, the formula returns its code from column A.
The return range comes first in INDEX; MATCH searches the product-name range.
Return a value at a row and column intersection
For a two-way lookup, use one MATCH to identify the row and another to identify the column.
Assume product names are in A2:A4, month headers are in B1:D1, and sales figures occupy B2:D4.
If the product is in F2 and the month is in G2, use:
=INDEX(B2:D4, MATCH(F2, A2:A4, 0), MATCH(G2, B1:D1, 0))
For example, if F2 is Notebook and G2 is March, the formula returns the figure where that product’s row meets the March column.
Keep the row labels, column headers, and data grid aligned.
Match using more than one criterion
To find a row using both a person and a month, multiply the criteria tests inside MATCH.
Suppose names are in A2:A6, months are in B2:B6, and hours are in C2:C6.
With the requested name in E2 and month in F2, use:
=INDEX(C2:C6, MATCH(1, (A2:A6=E2)*(B2:B6=F2), 0))
The two comparisons produce arrays of TRUE/FALSE values; multiplication converts rows meeting both criteria to 1.
MATCH looks for that 1, and INDEX returns the hours from the corresponding row.
In Microsoft 365 and current Google Sheets, this pattern can be entered normally.
Older Excel versions may require array entry with Ctrl+Shift+Enter; behavior can depend on version and array calculation support.
Approximate match for sorted data
For bracket or threshold lookups, MATCH can return the position of an exact value or, when the exact value is absent, the next smaller value.
The lookup range must be sorted in ascending order.
For example, if minimum scores are in A2:A5 as 0, 60, 70, and 80, and grade labels are in B2:B5, use:
=INDEX(B2:B5, MATCH(D2, A2:A5, 1))
If D2 is 75, the match position corresponds to 70, so INDEX returns the label in the same row as 70.
Use this mode only when the lookup range is sorted correctly; unsorted values can lead to an incorrect result.
For an exact lookup, use 0 instead.
Handle missing matches with IFERROR
If MATCH cannot find the lookup value, the formula returns an error, commonly #N/A.
Wrap the lookup in IFERROR to show a clearer message:
=IFERROR(INDEX(B2:B5, MATCH(D2, A2:A5, 0)), "Not found")
This returns the matching department when found and Not found when an error occurs.
IFERROR catches errors from any part of the expression, so it can also hide issues beyond a missing value.
If you need to distinguish missing data from other formula errors, inspect the source ranges and consider a more targeted error check.
Common INDEX MATCH problems
- Ranges have different sizes: Keep the lookup and return ranges the same height for a vertical lookup, or the same width for a horizontal lookup.
- Exact-match argument is missing: Include
0as MATCH’s third argument for an exact search.
Leaving it out invokes approximate matching behavior. - Numbers and text do not agree: A number stored as text may not match a numeric value.
Check cell formats and underlying values, not just how they appear. - Extra spaces or hidden characters: Clean inconsistent source text with functions such as TRIM where appropriate.
- Duplicate lookup values: MATCH returns the position of the first matching entry.
If duplicates exist, make the lookup key more specific or use a multiple-criteria formula. - Wrong orientation: INDEX’s row and column arguments refer to positions inside its selected array.
In a one-column return range, MATCH should supply the row number.
Excel and Google Sheets compatibility
INDEX and MATCH are established functions available in modern Excel and Google Sheets, and the basic exact-match patterns work in both.
The multiple-criteria array pattern can vary with older Excel versions, particularly where dynamic arrays are not supported.
If a formula behaves differently in a legacy workbook, check the Excel version and whether array formulas need special entry.
Also verify that regional settings use the correct argument separator: some installations require semicolons instead of commas.