INDEX MATCH Generator
Build flexible INDEX and MATCH lookup formulas that can return values from either side of your lookup range.
INDEX MATCH
Create a flexible lookup formula that does not require lookup values in the first column.
Range containing the values to return.
Cell or value to find.
Range to search for the lookup value.
=INDEX($B$2:$B$100, MATCH(A2, $C$2:$C$100, 0))Checks Before You Paste
- •INDEX MATCH can return values from columns to the left of the lookup range.
- •Use ranges with the same number of rows.
- •The final 0 in MATCH requests an exact match.
How INDEX MATCH works
Availability: INDEX and MATCH have both shipped in every version of Excel since 1993, in Excel for Mac and the web, in Google Sheets and in LibreOffice Calc. This is the lookup to build when the workbook has to open anywhere.
INDEX and MATCH are two independent functions doing two separate jobs. MATCH searches a single row or column and returns a position - not a value, a number saying how far into the range the match sits. INDEX takes a range and a position and hands back the cell at that offset. Nesting them, =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), gives a lookup where the searched column and the returned column are named separately and can sit in any order on the sheet.
That separation is the whole advantage over VLOOKUP. The return range can be to the left of the lookup range, which VLOOKUP cannot do at all. Both ranges are real references, so inserting or deleting a column between them moves them with it instead of leaving a hard-coded column number pointing at the wrong field. And because INDEX only ever reads the one column you gave it, a lookup across a wide table does not drag the whole table into the calculation.
MATCH's third argument decides the search. 0 means exact match and is the only one that is safe on unsorted data; it scans the range in order and returns #N/A when nothing matches. 1, or omitting the argument, finds the largest value less than or equal to the lookup value and requires the range sorted ascending. -1 finds the smallest value greater than or equal to it and requires the range sorted descending. With match_type 0, MATCH also accepts wildcards - "Ltd*" and "J?n" both work - which is something exact-match VLOOKUP does too but XLOOKUP requires a mode flag for.
The pair also does what VLOOKUP calls a two-way lookup without any extra machinery: give INDEX both a row number and a column number, each from its own MATCH, and one formula reads a value out of a grid by its row label and its column header. Matching is case-insensitive throughout, and MATCH treats a reference to an empty cell as 0 in the same way VLOOKUP does.
Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, [match_type]))| Argument | Required | What it does |
|---|---|---|
return_range | Required | The row or column INDEX reads the answer from. It must line up with lookup_range row for row; a range that starts on a different row returns the wrong record with no error. |
lookup_value | Required | What MATCH searches for. With match_type 0 this may contain the wildcards * and ?, escaped with ~ for a literal one. |
lookup_range | Required | The single row or column MATCH searches. A two-dimensional range returns #N/A - MATCH only walks one line at a time. |
match_type | Optional | 0 for an exact match, on data in any order. 1 or omitted needs ascending data and returns the largest value at or below the target. -1 needs descending data and returns the smallest value at or above it. |
More worked examples
A staff sheet lists email addresses in column B and payroll IDs in column A. G2 holds an address and you need the ID that sits to its left.
=INDEX($A$2:$A$400, MATCH(G2, $B$2:$B$400, 0))Returns: The payroll ID on the row whose email matches G2, or #N/A when the address is not on the sheet.
There is no equivalent VLOOKUP here: the answer is one column to the left of the search column.
A rate grid has months across row 1 (B1:M1) and product names down column A (A2:A30), with rates in B2:M30. You want the rate for Widget in March.
=INDEX($B$2:$M$30, MATCH("Widget", $A$2:$A$30, 0), MATCH("Mar", $B$1:$M$1, 0))Returns: The single cell where the Widget row crosses the Mar column.
INDEX's second and third arguments are row and column offsets inside the grid, so each MATCH counts within its own strip rather than from column A of the sheet.
Shipping bands in D2:D6 hold weights 0, 1, 5, 20 and 50 in ascending order, with prices in E2:E6. A parcel weighs 7.4 kg, held in B2.
=INDEX($E$2:$E$6, MATCH(B2, $D$2:$D$6, 1))Returns: The price on the 5 kg band, because 5 is the heaviest band the parcel clears.
match_type 1 is only correct because D2:D6 is sorted ascending. Sorted the other way it returns the wrong band and never errors.
Common mistakes
- Ranges that do not start on the same row
- MATCH returns a position, so INDEX($A$1:$A$400, MATCH(G2,$B$2:$B$400,0)) is off by one on every row: MATCH counts from B2 while INDEX counts from A1. There is no error, just the neighbouring record. Keep both ranges on identical row bounds.
- Giving MATCH a two-dimensional range
- MATCH searches one row or one column. Pass it $B$2:$D$400 and it returns #N/A regardless of what is in there. If you need to search a block, use two MATCHes with INDEX's row and column arguments instead.
- Leaving match_type off
- =MATCH(G2,$B$2:$B$400) defaults to 1, an approximate match that assumes ascending order. On a list of names or codes that returns whichever position the search lands on rather than #N/A. The trailing 0 is not optional in practice.
- Half-absolute references when filling down
- If the ranges are written relative, filling the formula down shifts them a row at a time and the bottom of the list starts reporting #N/A. Lock both with $ signs, or reference an Excel Table column, which expands as rows are added.
- Recalculating MATCH twice for the same row
- Pulling three fields from one record with three INDEX/MATCH pairs makes Excel search the lookup column three times. On a large sheet that is the difference between instant and sluggish. Put the MATCH in its own helper cell and feed its result to each INDEX.
Frequently Asked Questions
Three reasons that matter in a workbook that lives for years: it can return columns to the left, it survives column insertions because both arguments are references rather than a counted number, and it reads only the two columns named rather than the whole table between them.
With match_type 0 it returns the position of the first one it meets, scanning top to bottom or left to right. To get the last one, either search a reversed range or switch to XMATCH with search_mode -1.
Yes, by matching a concatenation: =INDEX(D2:D400, MATCH(G2&H2, A2:A400&B2:B400, 0)). In Excel 2019 and earlier that has to be confirmed with Ctrl+Shift+Enter because the joined ranges are an array; from Excel 2021 it just works. A safer alternative is a helper column holding A2&"|"&B2 so the separator stops "AB"+"1" colliding with "A"+"B1".
It returns a reference to the cell, which is why =SUM(A1:INDEX(A1:A100, 20)) works as a range and why INDEX can sit on either side of a colon. In ordinary use the reference is read as a value, so the distinction only surfaces in dynamic-range tricks.
Because the matched cell really is empty, and Excel reads an empty cell as 0. Wrap it to keep the blank look: =IF(INDEX(...)="", "", INDEX(...)), or format the result cell to hide zeros if the value never has to be genuinely empty.