An INDEX MATCH generator writes the nested formula from three inputs: the value to find, the column to search, and the column to bring back. A typical case is the lookup VLOOKUP can't do. A payroll sheet keeps employee IDs in column A and work emails in column D, and HR sends you a list of emails that need their IDs. That's a lookup to the left. Type F2, D2:D500 and A2:A500, and the generator gives you =INDEX($A$2:$A$500,MATCH(F2,$D$2:$D$500,0)), which works in every version of Excel.
How to use the INDEX MATCH generator
- Type the lookup value: a cell such as
F2, a number, or text. Text gets its quotes added for you.
- Type the lookup range (the one column to search, like
A2:A100) and the return range (the column to bring back, like C2:C100). The return range can sit to the left of the lookup range.
- Pick the match type. Exact (0) is the default. "Lock the ranges with $" is ticked by default too; leave it on if you'll fill the formula down.
- For a two-way lookup, tick "Two-way lookup", make the return range the whole table (
B2:E100), then type the column heading to find and the header row it sits in (B1:E1).
- Read the line under each part of the formula, click "Copy formula", paste into your first result cell and fill down.
There's no file to download. The formula is built in your browser as you type, nothing is uploaded, and Copy formula puts exactly the formula text on the clipboard.
What the generator writes
| Your choices | The formula |
|---|
| One-way, exact match | =INDEX($C$2:$C$100,MATCH(F2,$A$2:$A$100,0)) |
Two-way, heading typed as "Q3" | =INDEX($B$2:$E$100,MATCH(F2,$A$2:$A$100,0),MATCH("Q3",$B$1:$E$1,0)) |
| One-way, match type 1 | =INDEX($C$2:$C$100,MATCH(F2,$A$2:$A$100,1)) |
| "Lock the ranges with $" unticked | =INDEX(C2:C100,MATCH(F2,A2:A100,0)) |
The match type is always written out, including the 0. MATCH with its third argument left off defaults to 1, an approximate match, and on an unsorted list that returns a plausible wrong row instead of #N/A. Nothing on the sheet looks broken.
Pick 1 or -1 on purpose and you get a warning instead. Type 1 finds the largest value less than or equal to the lookup value, which suits commission tiers or shipping bands, and needs the lookup range sorted smallest to largest. Type -1 needs it sorted largest to smallest. On unsorted data, type 1 returns a wrong row with no error, and -1 returns a wrong row or #N/A.
Lookup values follow the same rules as our other formula generators. Cells, numbers and TRUE/FALSE go in as they are. Other text is quoted, with any quote inside it doubled. Start with = to pass a defined name or expression through as written. The $ locking skips names and table references.
Before it shows a formula, the generator checks the shapes. The lookup range must be a single column (or row). The return range, or the table in two-way mode, must start and end on the same rows as the lookup range. In one-way mode, a return range wider than one column is rejected, with a suggestion to tick two-way lookup. The header row must be one row covering the same columns as the table. Defined names and tables can't be measured, so they go through unchecked.
The two ranges have to start on the same row
MATCH returns a position, not a row number. If MATCH(F2,$A$2:$A$100,0) returns 5, that's the fifth cell of A2:A100, which is row 6. INDEX then takes the fifth cell of its own range. Give it C3:C101 and the fifth cell is C7, one row down, so every result is the next person's value. Excel shows no error.
We measured it in Excel for Mac, with codes in A2:A4 and values in C2:C4. =INDEX(C2:C4,MATCH("G7",A2:A4,0)) returned 20, the right answer. =INDEX(C3:C5,MATCH("G7",A2:A4,0)) returned 30, the value from the row below, with no error.
The generator checks where each range starts and ends. C3:C101 against A2:A100 has the same 99 rows but gets rejected, and the message names the rows each range covers.
The header row in a two-way lookup gets the same check by column. Over a table of B2:E100, the header row has to be B1:E1. A1:D1 is the right width but starts one column early, which would return the column to the right of every heading, so the generator rejects it.
How a two-way lookup finds the column by its heading
A two-way lookup, often called INDEX MATCH MATCH, finds both the row and the column. Take a sales grid with regions in A2:A100 and quarters Q1 to Q4 across B1:E1. The first MATCH finds the region's row, the second finds the quarter's column, and INDEX returns the cell where they cross. The generator always makes the column MATCH an exact match.
Headings that look like cell addresses. Q3 is column Q, row 3. FY24 is column FY, row 24. Type Q3 as the heading and the generator reads it as a cell, the same way Excel would. The explanation line under the formula then tells you to type it in quotes if it's a heading, and the help text under the field says the same. Typed as "Q3", it goes in as text.
Headings that are dates or numbers. The column MATCH compares values, not what the cell displays. A header row of real dates formatted to show Jan, Feb, Mar won't match the text "Jan". A year typed as 2024 goes into the formula as a number, which matches a numeric header but not one stored as text. The generator can't see your header row, so it can't tell which kind you have. The dependable fix is to point the heading field at a cell, such as G1, that holds the same kind of value as the headers.
When INDEX MATCH says #N/A for a value you can see
Exact match returns #N/A when the two values differ, even invisibly. =F2=A7 returns FALSE when two cells only look the same, and =LEN(F2) counts characters you can't see. These are the usual culprits:
- A number stored as text on one side. Codes like 10442 exported from another system often arrive as text, and MATCH won't treat text "10442" as the number 10442. Select the text column and run Data > Text to Columns > Finish to turn it into numbers. Or, if the lookup column is the text one, type
=F2&"" as the lookup value: the generator writes MATCH(F2&"",...), which looks up the text version of F2.
- A trailing space. "Acme Ltd " and "Acme Ltd" don't match. TRIM in a helper column removes it.
- A line break carried over from an export. CLEAN strips it.
- A non-breaking space, common in text pasted from a web page. Neither TRIM nor CLEAN removes it;
=SUBSTITUTE(A2,CHAR(160)," ") does.
The generator builds the formula without reading your data, so this one is for Excel to answer.
INDEX MATCH vs VLOOKUP, tested in Excel
We checked the inserted-column claim in Excel for Mac on a table in A:E. =INDEX($C$2:$C$3,MATCH(G2,$A$2:$A$3,0)) and =VLOOKUP(G2,$A$2:$E$3,3,FALSE) both returned 21. After inserting a column at B, Excel rewrote the INDEX MATCH as =INDEX($D$2:$D$3,MATCH(H2,$A$2:$A$3,0)), still 21. The VLOOKUP returned 11 from the wrong column, with no error. The two-way formula returned 31 before and after, and a missing value returned #N/A.
If you're sure the table's columns will never move, the VLOOKUP generator writes the shorter formula. On Microsoft 365 or Excel 2021 or later, the XLOOKUP generator does one-way lookups in a single function, but it doesn't build a two-way lookup, and XLOOKUP shows #NAME? in Excel 2019 and earlier.
Writing INDEX MATCH in Excel without the generator
Typing it straight into the cell is usually quicker than the dialogs. Type =INDEX(, drag across the return column, type ,MATCH(, click the lookup cell, type a comma, drag across the lookup column, then type ,0)). Select each range in the formula bar and press F4 (Cmd+T on a Mac) to add the $ signs. On Windows, Formulas > Lookup & Reference > INDEX also works, but it first asks which argument list you want (pick the array form), and nesting MATCH inside the Function Arguments box takes more clicks than typing it.
That route is the only one for a multiple-criteria lookup, since the generator doesn't build one.
What the generator can't do
- No IFERROR option. Wrap the formula by hand, as in
=IFERROR(INDEX($C$2:$C$100,MATCH(F2,$A$2:$A$100,0)),"Not found"), and check it works without the wrapper first: IFERROR hides every error, including a mistyped range.
- No multiple-criteria MATCH. The pattern is
MATCH(1,($A$2:$A$100=F2)*($B$2:$B$100=G2),0), entered with Ctrl+Shift+Enter in Excel 2019 and earlier.
- The column MATCH in two-way mode is always exact.
- It separates arguments with commas. If your Excel uses semicolons, as many European setups do, replace them.
- It doesn't see your data, so it can't tell whether the value exists or what type your headers are.
The INDEX function and MATCH function pages cover each half on its own, and INDEX and MATCH: two-dimensional lookups works through more grid examples.
Questions
Why use INDEX MATCH instead of VLOOKUP?
Because INDEX MATCH points at the column to return instead of counting to it, so inserting a column in the table doesn't change the answer. In our Excel test, a VLOOKUP returned the wrong column with no error after a column was inserted, while the INDEX MATCH kept returning the right value. INDEX MATCH can also return a column to the left of the one it searches, which VLOOKUP can't.
What is INDEX MATCH MATCH?
It's a two-way lookup: one MATCH finds the row, a second MATCH finds the column, and INDEX returns the cell where they cross. For a sales grid with regions down column A and quarters across row 1, =INDEX($B$2:$E$100,MATCH(F2,$A$2:$A$100,0),MATCH("Q3",$B$1:$E$1,0)) returns the Q3 figure for the region in F2. The header row has to cover exactly the same columns as the table.
Why does INDEX MATCH return #N/A when the value is there?
Because the two values aren't identical, even though they look it. The usual causes are a number stored as text on one side, a trailing space, or a line break carried over from an export. Test with a formula like =F2=A7, which returns FALSE when two cells only look alike. TRIM or CLEAN in a helper column removes stray characters, and Data > Text to Columns > Finish turns numbers stored as text back into numbers.
Why does INDEX MATCH return 0 instead of a blank?
Because MATCH found the row and the cell INDEX returns is empty, and Excel shows an empty cell's value as 0. IFERROR won't help, since there's no error to catch. In Excel 365 or 2021, wrap it as =LET(x,INDEX($C$2:$C$100,MATCH(F2,$A$2:$A$100,0)),IF(x="","",x)) to show a blank instead.
How do I use INDEX MATCH with multiple criteria?
Match the number 1 against an array that multiplies the conditions together: =INDEX($C$2:$C$100,MATCH(1,($A$2:$A$100=F2)*($B$2:$B$100=G2),0)). Each comparison returns TRUE or FALSE, and the multiplication turns a row where both are TRUE into 1. Press Enter in Excel 365 or 2021, or Ctrl+Shift+Enter in Excel 2019 and earlier. The generator doesn't build this form, so type it into Excel directly.
What does MATCH return when the value appears more than once?
The position of the first match from the top, so INDEX MATCH returns the first matching row's value. To get the last match in Excel 365 or 2021, replace MATCH with XMATCH and a search mode of -1: XMATCH(F2,$A$2:$A$100,0,-1).
Should I use XLOOKUP instead of INDEX MATCH?
For a one-way lookup, XLOOKUP is shorter, but only if everyone who opens the file has Microsoft 365 or Excel 2021 or later. Excel 2019 and earlier show #NAME? for XLOOKUP, while INDEX MATCH works in every version. For a lookup by row and column heading, INDEX MATCH MATCH remains the standard formula.