INDEX MATCH lookup
=INDEX(C:C, MATCH(E2, A:A, 0)) returns price from column C where column A matches E2.
What is INDEX in Google Sheets?
INDEX fetches a value from a range by row and column number. Combined with MATCH it powers flexible lookups. Alone it picks nth items from lists, dynamic headers, or matrix intersections without VLOOKUP column limits.
When to use it
Use INDEX with MATCH for left lookups, two-way table intersections, or pulling the nth match from a sorted list.
When to skip it
Skip INDEX/MATCH when XLOOKUP covers the case in one function with clearer not-found handling.
How it works
-
1
Pass the array or reference as the first argument.
-
2
Supply row index and optional column index for two-dimensional ranges.
-
3
Use MATCH to find row or column positions dynamically from keys.
-
4
Use INDEX(range, 0, col) or INDEX(range, row, 0) to return whole row or column in some contexts.
-
5
Lock lookup tables with absolute references when copying formulas.
-
6
Verify row numbers after sorting or filtering does not scramble static INDEX positions.
Examples in Google Sheets
Two-way table
=INDEX(B2:F20, MATCH(H1, A2:A20, 0), MATCH(H2, B1:F1, 0)) crosses row and column headers.
Nth item
=INDEX(SORT(UNIQUE(B2:B)), 3) returns the third distinct value after sort.
Build this without starting from a blank cell
Use a Better Sheets tool for INDEX, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Hard-coded row numbers after inserting rows above the table.
- MATCH third argument wrong for approximate vs exact match.
- INDEX column number off by one after inserting a column left of return range.
- Referencing whole columns A:A inside INDEX on huge sheets for no reason.
- Ignoring #N/A from MATCH when key is missing without IFERROR.
Frequently asked questions
- INDEX vs VLOOKUP?
- INDEX with MATCH can look left and avoids VLOOKUP column index fragility.
- INDEX vs XLOOKUP?
- XLOOKUP simplifies many INDEX MATCH patterns with one function.
- Return entire row?
- Use row index with appropriate column index 0 form per Sheets rules for row slices.
- INDEX with multiple MATCH?
- Rare patterns need FILTER instead when multiple keys match.
- Why #REF!?
- Row or column index out of bounds of the array.
- INDEX on spilled array?
- Yes. INDEX can pick elements from dynamic array output.
- INDEX two-dimensional?
- Provide both row and column indexes for matrix ranges.
Related Tutorials
Watch how INDEX works
Use Index Match when you want Vlookup
How to Merge Cells in Google Sheets
200 REGEXMATCH Examples in Google Sheets
Related blog posts
Guides that explain INDEX in more depth.
7 Best Beginner Tutorials Featuring the INDEX() Google Sheet Formula
Master Google Sheets with 7 beginner tutorials on using the INDEX formula. Turn data into results with free tools, templates, and tutorials!
Read post →6 Different Ways to Use INDEX()
This is the syntax for the INDEX formula. Below are video tutorials that talk about the INDEX formula, These are fun tutorials that show you how you can use INDEX for different cases and uses.
Read post →3 Advanced Ways to Use the INDEX Formula
Three video tutorials for advanced users. In these tutorials, I show you the different ways you can make use of the INDEX function to create fun and useful things with Google Sheets.
Read post →Related glossary terms
MATCH
MATCH searches a range for a value and returns its relative position. Feeding INDEX or OFFSET with MATCH replaces brittle column index math. Sorted approximate match modes support tier tables and date brackets.
Read guide →OFFSET
OFFSET starts from a reference cell and returns a range moved by a row and column offset with optional height and width. Rolling averages, dynamic windows, and shifted blocks use OFFSET, though INDEX and FILTER are often clearer today.
Read guide →XLOOKUP
XLOOKUP searches a lookup array for a key and returns a matching value from a return array. It handles left lookups, approximate match, and custom not-found text without INDEX/MATCH gymnastics.
Read guide →VLOOKUP
VLOOKUP searches the first column of a range for a key and returns a value from a specified column to the right. It is still everywhere in legacy templates even as XLOOKUP offers more flexible replacements.
Read guide →Done reading about INDEX?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.