For decades, the VLOOKUP function was the undisputed king of Microsoft Excel. It allowed users to search for a value in one column and return a corresponding value from another column. However, VLOOKUP has a fatal flaw: it only searches from left to right. If the data you want to retrieve is located to the left of your search column, VLOOKUP breaks completely.
While Microsoft recently introduced XLOOKUP to fix this issue, millions of corporate spreadsheets running on older versions of Excel still rely on a more powerful, bulletproof workaround: combining the INDEX and MATCH functions.
Understanding the Two Functions
To understand why this combination is so powerful, you must first understand what the two functions do individually.
1. The MATCH Function (The GPS)
The MATCH function’s only job is to find the physical location (the row number) of a specific item in a list. It does not return data; it returns a coordinate.
Syntax: =MATCH(lookup_value, lookup_array, [match_type])
If you have a list of employee ID numbers in column B, and you ask MATCH to find ID “4599”, it will scan the column and report back: “That ID is in row 12.”
2. The INDEX Function (The Retriever)
The INDEX function’s only job is to go to a specific coordinate and retrieve whatever data is sitting there.
Syntax: =INDEX(array, row_num, [column_num])
If you tell INDEX to look at the Employee Names in column A, and go to row 12, it will fetch the name “Sarah Connor”.
Combining Them into a Super Formula
By nesting the MATCH formula inside the INDEX formula, you create a dynamic retrieval system that does not care about left-to-right order. You use MATCH to dynamically calculate the row number, and feed that number directly into INDEX.
The combined syntax always follows this pattern:
=INDEX(Column_To_Return, MATCH(Search_Value, Column_To_Search, 0))
A Real-World Example
Imagine a spreadsheet where Column A contains Employee Names, and Column B contains their Employee IDs. You have a blank cell (D2) where you type an ID number, and you want cell E2 to automatically display the correct Name.
Because the Name (Column A) is to the left of the ID (Column B), a standard VLOOKUP will fail. Here is how you build the INDEX MATCH formula in cell E2:
- Start the Retriever: Type
=INDEX( - Select the Target Data: Highlight the column containing the data you want as your final answer. In this case, highlight the Names in Column A (
A:A). Type a comma. - Start the GPS: Type
MATCH( - Select the Search Query: Click on the cell where you type the ID number (
D2). Type a comma. - Select the Search Area: Highlight the column where Excel should look for that ID number. In this case, Column B (
B:B). Type a comma. - Force an Exact Match: Type
0. This is critical. It tells Excel to find an exact match, not an approximation. - Close the brackets: Type two closing parentheses
))to seal both formulas.
Your final formula will look like this:
=INDEX(A:A, MATCH(D2, B:B, 0))
Why This is Better Than VLOOKUP
Aside from being able to look left, INDEX MATCH is vastly superior for data integrity. In a standard VLOOKUP, you must manually type a column index number (e.g., “return data from the 4th column”). If a colleague inserts a new column in the middle of your spreadsheet, the 4th column becomes the 5th, and your VLOOKUP silently starts returning the wrong data.
Because INDEX MATCH references specific columns directly (A:A and B:B), you can insert, delete, or rearrange columns all day long, and the formula will automatically adjust itself without ever breaking.