INDEX MATCH Basic Example in Excel and Google Sheets
Purpose
INDEX MATCH is the lookup combination that power users swear by. While VLOOKUP is simpler to write, INDEX MATCH is…
INDEX MATCH is the lookup combination that power users swear by. While VLOOKUP is simpler to write, INDEX MATCH is fundamentally more flexible — it can look left or right, it is not affected by column insertions, and it handles large datasets more efficiently. I made the switch from VLOOKUP to INDEX MATCH years ago and have not looked back. The key insight is that these are two separate functions working together: MATCH finds the position of your lookup value in a column, and INDEX uses that position to retrieve a value from any other column you choose. Once you understand the two-step logic, INDEX MATCH becomes completely intuitive and you will wonder why you ever limited yourself to VLOOKUP.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Arguments
| Argument | Required | Description |
|---|---|---|
| return_range (INDEX) | Required | The column or range containing the value you want to retrieve. This can be any column — to the left, right, or anywhere in your spreadsheet. This is what makes INDEX MATCH more flexible than VLOOKUP. |
| row_num (INDEX via MATCH) | Required | The row position to retrieve from return_range. You supply this by nesting MATCH here rather than typing a number. MATCH calculates the position dynamically based on your lookup value. |
| lookup_value (MATCH) | Required | The value you are searching for. MATCH looks for this in the lookup_range and returns the row number where it was found. |
| match_type (MATCH) | Optional | 0 for exact match which is what you need for IDs, codes, and names. 1 for largest value less than or equal. -1 for smallest value greater than or equal. Almost always use 0. |
How it works
INDEX MATCH works in two logical steps. First, MATCH scans your lookup range to find where your lookup value is located, returning a row number. For example, if you search for EMP-003 in a list of employee IDs and it appears in the fifth row of that list, MATCH returns 5. Second, INDEX takes that row number and retrieves the value from the corresponding row in your return range. If your return range is the Department column, INDEX(Dept_column, 5) returns whatever department is in row 5 of that column. The two ranges — lookup range and return range — must be the same length and aligned to the same rows, but they can be in completely different parts of your spreadsheet or even different sheets. The critical advantage over VLOOKUP is that your return column can be anywhere — to the left, to the right, or even in a completely different table on another sheet. You are not restricted to looking right from a first column. This also means inserting or deleting columns never breaks INDEX MATCH, whereas VLOOKUP with a hardcoded col_index_num breaks immediately when columns shift.
Examples
1
Look up the department for each employee ID from the HR directory.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Lookup ID | Employee ID | Name | Department | Result |
| 2 | EMP-003 | EMP-001 | Alice Chen | Engineering | =INDEX(D:D,MATCH(A2,B:B,0)) |
| 3 | EMP-002 | Bob Smith | Marketing | ||
| 4 | EMP-003 | Carol Jones | Finance | Finance | |
| 5 | EMP-004 | David Kim | HR | ||
| 6 | EMP-005 | Eve Taylor | Sales | ||
| 7 | EMP-006 | Frank Brown | Engineering | ||
| 8 | EMP-007 | Grace Lee | Marketing | ||
| 9 | EMP-008 | Hank Patel | Finance | ||
| 10 | EMP-009 | Iris Wong | HR | ||
| 11 | EMP-010 | James Miller | Sales |
Row 2: Finance
— MATCH found 89.50 in column C at position 2 (row 2 of the data). INDEX returned PRD-003 from column A at the same position.
MATCH found EMP-003 in position 3 of the employee ID column. INDEX then retrieved the value from position 3 of the Department column. The return column D is to the right of the lookup column B in this example, but it could equally be to the left — that is the key difference from VLOOKUP which can only look right. If EMP-003 did not exist in column B, MATCH would return #N/A and INDEX would pass that through.
2
Find a product code by looking up its price — a left-lookup that VLOOKUP cannot do.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product Code | Product Name | Price | Lookup Price | Code Result |
| 2 | PRD-003 | Monitor Stand | 89.50 | 89.50 | =INDEX(A:A,MATCH(D2,C:C,0)) |
| 3 | PRD-001 | Wireless Mouse | 29.99 | ||
| 4 | PRD-002 | USB Keyboard | 45.99 | PRD-003 | |
| 5 | PRD-004 | Laptop Bag | 59.99 | ||
| 6 | PRD-005 | HDMI Cable | 12.99 | ||
| 7 | PRD-006 | Webcam HD | 79.00 | ||
| 8 | PRD-007 | Desk Lamp | 34.50 | ||
| 9 | PRD-008 | USB Hub | 27.99 | ||
| 10 | PRD-009 | Mouse Pad XL | 19.99 | ||
| 11 | PRD-010 | Headset Pro | 129.00 |
Row 2: PRD-003
— MATCH found 89.50 in column C at row 1. INDEX returned PRD-003 from column A at the same row.
This left-lookup is impossible with VLOOKUP. INDEX MATCH searches any column and returns from any other column regardless of position.
3
Return a friendly message when the lookup ID does not exist in the table.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Lookup ID | Employee ID | Salary | Result |
| 2 | EMP-999 | EMP-001 | 62000 | =IFERROR(INDEX(C:C,MATCH(A2,B:B,0)),"Not found") |
| 3 | EMP-002 | 48000 | ||
| 4 | EMP-003 | 55000 | Not found | |
| 5 | EMP-004 | 45000 | ||
| 6 | EMP-005 | 71000 | ||
| 7 | EMP-006 | 53000 | ||
| 8 | EMP-007 | 58000 | ||
| 9 | EMP-008 | 42000 | ||
| 10 | EMP-009 | 67000 | ||
| 11 | EMP-010 | 39000 |
Row 2: Not found
— EMP-999 does not exist in column B so MATCH returns #N/A and IFERROR returns Not found.
Always wrap INDEX MATCH in IFERROR in production spreadsheets. Unhandled #N/A errors break downstream calculations.
Common use cases
1. Look up an employee salary from a column to the left of the ID column — impossible with VLOOKUP
=INDEX(A:A,MATCH(D2,C:C,0))
2. Retrieve a product name from a product code where the code column is not the first column of the table
=INDEX(ProductNames,MATCH(A2,ProductCodes,0))
3. Cross-reference order numbers between two separate tables to find the matching delivery date
=INDEX(DeliveryDates,MATCH(B2,OrderNumbers,0))
4. Find the manager name for an employee where both columns are on a different sheet
=INDEX(HR!B:B,MATCH(A2,HR!A:A,0))
5. Look up the latest price for a product without being affected by column insertions in the price table
=INDEX(PriceTable[Price],MATCH(A2,PriceTable[Code],0))
Common errors
#N/A
MATCH could not find the lookup value in the lookup range. The value does not exist, there is a data type mismatch between text and numbers, or there are extra spaces in the lookup value or range.
Fix: Check the lookup value exists in the range. Use TRIM to remove spaces. Use VALUE or TEXT to align data types. Wrap in IFERROR to show a friendly message for genuine not-found situations.
#REF!
The row number returned by MATCH is larger than the number of rows in the INDEX return range, or one of the ranges has been deleted or moved.
Fix: Ensure the lookup range and return range cover the same rows and are the same length. Check that no columns or rows referenced by the formula have been deleted since the formula was written.
#VALUE!
The INDEX range is not a valid range reference, or MATCH is receiving a non-text value when it expects text, or the match_type argument contains a non-numeric value.
Fix: Check that all range references are valid and properly formatted. Confirm the match_type is 0, 1, or -1. Verify data types match between the lookup value and lookup range.
Tips and variations
Use column names with table references for robust formulas
If your data is in an Excel Table or you use named ranges, INDEX MATCH with named references never breaks when columns are inserted or moved. Instead of INDEX(C:C,MATCH(A2,B:B,0)) write INDEX(EmployeeTable[Department],MATCH(A2,EmployeeTable[ID],0)). The formula updates automatically if you restructure the table.
=INDEX(EmployeeTable[Department],MATCH(A2,EmployeeTable[ID],0))
Two-way lookup by combining two MATCH functions
INDEX MATCH can find a value at the intersection of a row and column — something VLOOKUP cannot do at all. Use one MATCH to find the row position and another to find the column position. This is perfect for looking up values in a matrix like a pricing table where rows are products and columns are regions.
=INDEX(PriceMatrix,MATCH(A2,Products,0),MATCH(B2,Regions,0))
Wrap in IFERROR for production-ready formulas
Any lookup formula should be wrapped in IFERROR in professional contexts so that missing values show a clean message rather than an error code. This is especially important in INDEX MATCH because the formula is often used in reports and dashboards where #N/A errors would confuse readers and break aggregate calculations.
=IFERROR(INDEX(C:C,MATCH(A2,B:B,0)),"Not found")
Excel vs Google Sheets
Excel vs Google Sheets
INDEX MATCH works identically in Microsoft Excel and Google Sheets. Same syntax, same MATCH match_type values, same behaviour. Both INDEX and MATCH are available in all versions of both applications going back many years. Formulas copy between Excel and Google Sheets without any changes. In Google Sheets, INDEX MATCH also works inside ARRAYFORMULA for array-based lookups across entire columns. The combination is equally portable whether you use column references, bounded ranges, or named ranges.
Frequently asked questions
VLOOKUP is simpler to write but has significant limitations: it can only search the first column of your table and return values from columns to the right, it breaks when columns are inserted, and col_index_num is fragile. INDEX MATCH can search any column and return from any other column including to the left, it is not affected by column structure changes, and it is generally faster on large datasets. For simple right-side lookups VLOOKUP is fine. For anything more complex, INDEX MATCH is the better choice.
VLOOKUP searches the entire table_array range even though it only needs the first column. INDEX MATCH separates the lookup range and return range so each function only processes the relevant column. On tables with hundreds of columns this can make a meaningful difference in recalculation speed. Additionally INDEX MATCH does not break when columns are inserted or deleted, reducing maintenance overhead in large files that change structure over time.
Yes — this is one of INDEX MATCH's most important advantages over VLOOKUP. The return range in INDEX can be any column anywhere in your spreadsheet. If your employee ID is in column C and you want to return the employee name from column A, INDEX MATCH handles this naturally: =INDEX(A:A,MATCH(D2,C:C,0)). VLOOKUP requires the lookup column to be leftmost which makes left-side lookups impossible.
Yes, INDEX MATCH is completely identical in both applications. Same syntax, same argument behaviour, same match_type values, same error handling. Formulas copy between Excel and Google Sheets without any changes needed. Both INDEX and MATCH have been available in both applications for many years, making this combination one of the most reliably portable lookup approaches available.
MATCH returns #N/A when the lookup value does not exist in the lookup range, and INDEX passes this error through. The standard approach is to wrap the entire formula in IFERROR: =IFERROR(INDEX(C:C,MATCH(A2,B:B,0)),"Not found"). Use an empty string "" instead of a message if you want the cell to appear blank. Use IFNA instead of IFERROR if you want to catch only not-found errors while still showing other error types that might indicate real formula problems.