The VLOOKUP Flaw
For twenty years, VLOOKUP was the most famous formula in Excel. However, it suffers from two catastrophic architectural flaws. First, it can only search from left to right. If your Employee ID numbers are in Column C, and their names are in Column A, VLOOKUP simply cannot find the name. Second, it relies on a hardcoded column index number (e.g., “return the data in the 4th column”). If a coworker inserts a new column in the middle of your spreadsheet, your VLOOKUP breaks entirely because the data is now in the 5th column.
While modern versions of Excel (Office 365) solved this by introducing the XLOOKUP function, millions of corporate computers are still running older, legacy versions of Excel (2016, 2019) that do not support XLOOKUP.
If you are stuck on an older version of Excel, you must abandon VLOOKUP and use the legendary combination of two separate formulas: INDEX and MATCH. Together, they create an unbreakable search engine that can search in any direction and completely ignores column insertions.
Understanding the Two Formulas
To understand the combination, you must understand what each formula does individually.
- MATCH: Tells you where something is. If you ask MATCH to find the name “John Smith” in a column, it will spit out a number (e.g., “He is in row 42”).
- INDEX: Tells you what is in a specific cell. If you ask INDEX to look at Column B and return whatever is in row 42, it will spit out the data (e.g., “$50,000”).
The trick is to nest the MATCH formula inside the INDEX formula. MATCH finds the row number, hands that number to INDEX, and INDEX returns the data.
The Syntax Structure
The combined formula looks intimidating, but the logic is actually simpler than VLOOKUP.
=INDEX([Return_Column], MATCH([Lookup_Value], [Search_Column], 0))Notice that unlike VLOOKUP, you are not selecting the entire table. You are strictly defining two independent columns.
Building the Formula (A Practical Example)
Imagine a spreadsheet where Employee Names are in Column A, and their Employee ID Numbers are in Column C. You want to type an ID Number into Cell F1, and have Cell F2 output the correct Name. VLOOKUP cannot do this because it requires searching right-to-left.
- Click into Cell F2 (where you want the Name to appear) and type
=INDEX(. - The first thing INDEX wants to know is where the final answer lives. The answer is a Name, so click the letter A at the top of the spreadsheet to highlight the entire Column A.
- Type a comma. Your formula now looks like:
=INDEX(A:A, - Now we need to find the row. Type
MATCH(. - The first thing MATCH wants to know is what we are looking for. Click on Cell F1 (where you will type the ID number). Type a comma.
- The second thing MATCH wants to know is where to look for that ID number. Click the letter C to highlight the entire Column C. Type a comma.
- MATCH asks for the match type. Always type exactly 0 (for an exact match).
- Close the parentheses twice to finish both formulas.
Your final, perfect formula is:
=INDEX(A:A, MATCH(F1, C:C, 0))Why It is Unbreakable
Hit enter. Test the formula by typing a valid ID number into F1. The correct name will instantly appear.
Now, test its resilience. Right-click on Column B and insert a blank column. The data in Column C shifts to Column D. If you look at your formula, Excel automatically updated it to read MATCH(F1, D:D, 0). Because you defined the exact columns rather than a hardcoded numerical index, the formula survives structural changes to the spreadsheet.
If you do not have access to XLOOKUP, VLOOKUP is a dangerous liability in a shared corporate spreadsheet. By taking the time to learn the syntax of INDEX MATCH, you build robust, omnidirectional search formulas that will never break when your coworkers reorganize the data.