How does the INDEX MATCH Formula Generator write a left lookup?

It places the column to return first in INDEX and the column you search in MATCH, so the answer can sit left of the search column. VLOOKUP cannot do this because it only returns columns to the right of its first column.

What is the difference between the array and no-array multiple criteria formulas?

Both multiply TRUE/FALSE tests and look for the number 1. The classic form needs Ctrl+Shift+Enter in Excel 2019 and earlier, while the version with INDEX(...,0) inside MATCH forces array evaluation, so a normal Enter works in every Excel version.

How does the two-way mode of the INDEX MATCH Formula Generator find a cell?

One MATCH finds the row position in your row labels and a second MATCH finds the column position in your headers. INDEX then returns the cell at that row and column of the data range, which makes it a lookup by two keys.

Which match type should I use in the INDEX MATCH Formula Generator?

Use exact (0) for IDs, names and codes. Use ascending (1) for the largest value not above the lookup on sorted data, such as tax bands, and descending (-1) for the smallest value not below it on data sorted high to low.

Can the INDEX MATCH Formula Generator return the second or third match?

Yes. Nth match mode builds an AGGREGATE version for Excel 2010 and later, a classic array version and an INDEX FILTER version for Excel 365 and Google Sheets. You choose n as a number or a cell reference.

Is my sheet data kept private when I use the INDEX MATCH Formula Generator?

Yes. The ranges, lookup values and sample cells stay in your browser and are evaluated on your device. Your last mode and settings, plus the cells of the test grid, are kept in this browser's local storage so the page reopens as you left it.

INDEX MATCH Formula Generator

Name: INDEX MATCH Formula Generator
Author: ArrayKit

Generate INDEX MATCH formulas for left lookups, two-way tables, multiple criteria and Nth matches, and test them on a live sample sheet without leaving your browser.

Every formula and every test result is produced in your browser. The ranges, values and sample cells you enter are never uploaded and nothing is sent to ArrayKit.

Built and maintained by Udit Agarwal

Open the VLOOKUP Formula Generator

About INDEX MATCH Formula Generator

INDEX MATCH is the lookup that does what VLOOKUP cannot: it searches any column, returns any other column, survives inserted columns and scales to several conditions. It is also easy to get wrong, because the return range and the lookup range must line up and the multi-criteria form needs array handling. This generator has five modes. Basic and left lookup write the classic pair. Two-way uses one MATCH for the row and another for the column. Multiple criteria offers the array form, a version that needs no Ctrl+Shift+Enter, a helper column and FILTER or XLOOKUP alternatives. Nth match returns the second or third occurrence. A sample sheet evaluates the result locally, and a comparison table explains when to prefer INDEX MATCH, VLOOKUP or XLOOKUP. Nothing you type is uploaded.

Features

Five modes: basic, left lookup, two-way, multiple criteria and Nth match
Exact, ascending (1) and descending (-1) match types with sorted-data warnings
Multiple criteria from one to four, as an array formula, a no-array version, a helper column, XLOOKUP and FILTER
Warns when the return and lookup ranges start on different rows or differ in height
IFERROR or IFNA wrapper, TRIM, text-and-number conversion and wildcard options
Comma or semicolon argument separator for Excel and Google Sheets in any locale
Live sample sheet that highlights the matched cell and the returned cell
Side-by-side comparison of INDEX MATCH, VLOOKUP and XLOOKUP

How to use the INDEX MATCH Formula Generator

Pick a mode: basic, left lookup, two-way, multiple criteria or Nth match
Enter the lookup value and the ranges, and lock them with the dollar-sign button
Choose the wrapper, trimming and separator options that suit your sheet
Copy the main formula, or one of the alternatives for other Excel versions or Google Sheets
Edit the sample sheet to confirm the result, then read the warnings if anything looks off

Example

Input

Mode:        Multiple criteria
Return:      $D$2:$D$100  (Sales)
Criterion 1: $A$2:$A$100 = F2  (South)
Criterion 2: $B$2:$B$100 = G2  (Apple)

Output

=IFERROR(INDEX($D$2:$D$100,MATCH(1,INDEX(($A$2:$A$100=F2)*($B$2:$B$100=G2),0),0)),"Not found")

Live test: South + Apple returns 95 (the first matching row)

The extra INDEX(...,0) removes the need for Ctrl+Shift+Enter in older Excel.

Common errors & troubleshooting

INDEX MATCH returns the value from the wrong row. — The return range and the lookup range start on different rows. INDEX uses the position MATCH found, so both ranges must begin on the same row and have the same height. The generator warns you when they do not.
The multiple-criteria formula shows #VALUE! or only the first cell in older Excel. — The classic array form needs Ctrl+Shift+Enter before Excel 365. Use the no-array version with INDEX(...,0), the helper column, or XLOOKUP, which all work with a normal Enter.
MATCH returns #N/A for a value that is clearly in the list. — Check for text stored as numbers and for trailing spaces. Turn on TRIM and the matching number or text conversion, and keep the match type on exact (0) unless the list is sorted.
The Nth match formula is empty or errors when n is too large. — There are fewer matches than the number you asked for. Wrap the formula in IFERROR; IFNA does not catch the error that this case produces.

Frequently asked questions

How does the INDEX MATCH Formula Generator write a left lookup?: It places the column to return first in INDEX and the column you search in MATCH, so the answer can sit left of the search column. VLOOKUP cannot do this because it only returns columns to the right of its first column.
What is the difference between the array and no-array multiple criteria formulas?: Both multiply TRUE/FALSE tests and look for the number 1. The classic form needs Ctrl+Shift+Enter in Excel 2019 and earlier, while the version with INDEX(...,0) inside MATCH forces array evaluation, so a normal Enter works in every Excel version.
How does the two-way mode of the INDEX MATCH Formula Generator find a cell?: One MATCH finds the row position in your row labels and a second MATCH finds the column position in your headers. INDEX then returns the cell at that row and column of the data range, which makes it a lookup by two keys.
Which match type should I use in the INDEX MATCH Formula Generator?: Use exact (0) for IDs, names and codes. Use ascending (1) for the largest value not above the lookup on sorted data, such as tax bands, and descending (-1) for the smallest value not below it on data sorted high to low.
Can the INDEX MATCH Formula Generator return the second or third match?: Yes. Nth match mode builds an AGGREGATE version for Excel 2010 and later, a classic array version and an INDEX FILTER version for Excel 365 and Google Sheets. You choose n as a number or a cell reference.
Is my sheet data kept private when I use the INDEX MATCH Formula Generator?: Yes. The ranges, lookup values and sample cells stay in your browser and are evaluated on your device. Your last mode and settings, plus the cells of the test grid, are kept in this browser's local storage so the page reopens as you left it.

Related tools

VLOOKUP Formula Generator — Build a correct VLOOKUP formula step by step, with IFERROR, exact or approximate match, XLOOKUP and INDEX MATCH alternatives and a live test.
Excel Formula Generator — Pick a task such as conditional sums, lookups, dates, text splitting or ranking and get a ready-to-paste Excel or Google Sheets formula.
Excel Formula Explainer — Paste any Excel or Google Sheets formula and get a plain-English, step-by-step breakdown of every function and argument.
Excel Data Cleaner — Clean messy spreadsheets: trim spaces, fix case, remove blanks and duplicates, standardise dates and numbers, then export .xlsx or CSV.
Excel to CSV — Convert an .xlsx or .xls spreadsheet to clean CSV or TSV, in your browser.
CSV to Markdown Table — Convert CSV or TSV data into a Markdown table, with alignment and header options.

Guides

VLOOKUP, INDEX MATCH and XLOOKUP, Explained

More Excel & Spreadsheets tools

Excel Formula Explainer — Paste any Excel or Google Sheets formula and get a plain-English, step-by-step breakdown of every function and argument.
Excel Formula Generator — Pick a task such as conditional sums, lookups, dates, text splitting or ranking and get a ready-to-paste Excel or Google Sheets formula.
Excel Function Translator — Translate Excel formulas between English and German, French, Spanish, Italian, Portuguese, Dutch, Swedish and Russian, with correct separators.
Excel VBA Macro Generator — Generate ready-to-run VBA macros from a library of tasks (loops, delete blank rows, export sheets to PDF, send mail and more) with your own ranges and names.
CSV to Excel Converter — Turn CSV or TSV files into a formatted .xlsx workbook in your browser: delimiter detection, header styling, column types and multiple sheets.
JSON to Excel Converter — Convert JSON arrays or nested objects into an Excel workbook with flattened columns, one sheet per array and typed cells.
Excel Data Cleaner — Clean messy spreadsheets: trim spaces, fix case, remove blanks and duplicates, standardise dates and numbers, then export .xlsx or CSV.
VLOOKUP Formula Generator — Build a correct VLOOKUP formula step by step, with IFERROR, exact or approximate match, XLOOKUP and INDEX MATCH alternatives and a live test.

See all Excel & Spreadsheets tools (9) · All tools