For years, VLOOKUP has been the standard function for finding data in Google Sheets. However, VLOOKUP has a massive, fundamental flaw: it can only search from left to right. If your search key is in Column C, a VLOOKUP cannot return data from Column A. It will simply fail.
Furthermore, VLOOKUP relies on a hard-coded column index number (e.g., return the 4th column). If a colleague inserts a new column into the middle of your dataset, the index number breaks, and the VLOOKUP returns the wrong data.
To perform resilient, two-way searches that never break, professional data analysts combine two distinct functions: INDEX and MATCH. In this guide, you will learn how to nest these functions to build a superior lookup formula.
Understanding the Two Functions
Before combining them, you must understand what each function does independently.
1. The MATCH Function
The MATCH function is a locator. You give it a word, and it tells you exactly what row that word is on.
=MATCH(search_key, range, search_type)If you search for the employee name “Sarah” in Column A, and Sarah is in cell A15, the MATCH function simply outputs the number 15.
2. The INDEX Function
The INDEX function is a retriever. You give it a column and a row number, and it returns whatever data is inside that cell.
=INDEX(range, row, column)If you tell the INDEX function to look at Column C, and pull the data from row 15, it will output Sarah’s salary.
Combining INDEX and MATCH
The brilliance of this combination is that you use the MATCH function to automatically calculate the row number, and feed that number directly into the INDEX function.
The syntax looks like this:
=INDEX(column_to_return, MATCH(search_key, column_to_search, 0))Note: The 0 at the end of the MATCH function forces an exact match, which is critical for accurate data retrieval.
Use Case: The Leftward Search
Imagine you have an inventory sheet. Column C contains the unique Product SKU, and Column A contains the Product Name. You want to type a SKU into cell F1 and have the formula return the Product Name.
Because the data you want (Column A) is to the left of your search key (Column C), a VLOOKUP is completely impossible here.
Instead, use INDEX and MATCH:
=INDEX(A:A, MATCH(F1, C:C, 0))Here is exactly how Google Sheets executes this:
- The MATCH function executes first. It takes the SKU from cell F1 and scans down Column C. It finds the SKU on row 42. It outputs the number 42.
- The formula now essentially becomes
=INDEX(A:A, 42). - The INDEX function looks at Column A, drops down to row 42, and extracts the Product Name.
Why This is Better than VLOOKUP
Aside from being able to search in any direction, this formula is incredibly resilient. Because you are referencing entire specific columns (A:A and C:C) rather than a giant block of data (A:F), the formula does not care if you insert new columns.
If someone inserts a new column between A and C, the data shifts, but the column references in your formula will automatically update to reflect the new layout. Your dashboard will remain perfectly functional, eliminating the brittle nature of traditional spreadsheet lookups.