When regularly working with large tables in Excel, you eventually encounter the limits of the VLOOKUP function. Between performance issues, rigidity regarding column positioning, and the inability to search to the left, you quickly feel constrained. This is where the INDEX and MATCH functions come into play: more flexible, faster, and capable of meeting advanced needs. In this article, we will explore in detail how these two formulas — sometimes combined — far exceed the capabilities of your old friend VLOOKUP function, while offering a much wider scope of action.
Why replace VLOOKUP?
Before diving into the heart of the matter, let’s take a quick overview. VLOOKUP remains very popular for its simplicity: you look for a value in the first column of a table, then retrieve data located a few columns to the right. However:
- Rigidity: the lookup column must be the first column of the table.
- Left lookup: impossible to retrieve information to the left of the reference column.
- Performance: on very large datasets, calculation times increase quickly.
- Error sensitivity: inserting or deleting columns can break formulas.
Faced with these constraints, it is natural to look for a more robust alternative. This is exactly where INDEX and MATCH come in.
Introduction to the INDEX and MATCH functions
Unlike VLOOKUP, these two functions are independent of each other and complement each other perfectly:
- INDEX returns the value of a cell located at the intersection of a row and a column within a range.
- MATCH returns the position (row or column number) of a searched value within a given range.
By combining their results, you get a two-way lookup on dynamic ranges, without worrying about column shifts.
The INDEX function
INDEX has two main syntaxes:
- INDEX(range; row_num; [column_num]): the most common. You provide a cell range, a row number, and optionally a column number.
- INDEX(reference; row_num; [column_num]; [area_num]): for multiple ranges, less commonly used daily.
Example: =INDEX(B2:D10; 4; 2) returns the value located at the 4th row and 2nd column of the range B2:D10. Simple, efficient, and completely decoupled from the location of your data in the sheet.
The MATCH function
MATCH uses this formula:
- MATCH(lookup_value; lookup_array; [match_type])
Where match_type is 0 for exact match, 1 for finding the largest value less than or equal to the lookup value, and -1 for the smallest value greater than or equal to the lookup value. Example: =EQUIV("Paris"; A2:A100; 0) returns the row number where “Paris” appears in column A.
How to combine INDEX and MATCH
The secret lies in the fact that MATCH finds the position, and INDEX the associated value. To get an equivalent of VLOOKUP, you simply nest:
=INDEX(plage_de_retour; EQUIV(valeur; plage_de_recherche; 0))
For example, if you want to find the salary of an employee listed in column A and the amounts are in column C: =INDEX(C2:C100; EQUIV("Dupont"; A2:A100; 0)). What took three steps with VLOOKUP now fits into a single line of formula, without dependency on the order of columns.
Advantages Compared to VLOOKUP
Beyond the obvious flexibility, here are some concrete benefits:
- Lookup to the left: no problem pointing to a column located before your criterion.
- Dynamic tables: if you insert or delete columns, nothing changes in your formulas.
- Improved performance: on hundreds of thousands of rows, INDEX/MATCH remains more responsive than VLOOKUP.
- Clearer reading: your formulas are explicit: you can directly see the return address and the reference range.
| Criterion | VLOOKUP | INDEX + MATCH |
|---|---|---|
| Lookup to the left | No | Yes |
| Robustness to added columns | Low | High |
| Performance | Average | Fast |
| Syntax complexity | Simple | Medium |
| Multi-criteria lookups | Limited | Possible (with arrays) |
Practical Examples
Simple Example
Imagine an inventory table where column A contains the product code and column D the current stock. With VLOOKUP, column A must be in position 1 and D in position 4. However, with:
=INDEX(D2:D500; EQUIV(B2; A2:A500; 0))
you can freely place the code anywhere. Here, B2 contains the code to search for and the result is displayed in the cell containing the formula.
Lookup with Two Criteria
To go further, it is possible to perform a combined lookup on two columns, for example product code and region. We use an array formula (or with the new UNIQUE/LET function if available):
=INDEX(D2:D1000; EQUIV(1; (A2:A1000=F1)*(B2:B1000=G1); 0))
With F1 and G1 as criteria cells, we enforce matching on both conditions. This unlocks complex scenarios where VLOOKUP cannot venture.
Tips and Best Practices
- Lock your ranges: use absolute references ($A$2:$A$100) to avoid moving your ranges when copying formulas.
- Simplify reading: give names to ranges (via the name manager) to make your formulas more readable.
- Check for errors: surround your formulas with IFERROR to avoid displaying #N/A.
- Optimize arrays: on large tables, favor limited ranges rather than entire columns.
Note: if you use Office 365 or Excel 2021, also explore the XLOOKUP function, which natively combines the power of INDEX/MATCH with simpler syntax.
Conclusion
Replacing VLOOKUP with the combination of INDEX and MATCH offers your workbooks greater flexibility, rock-solid robustness, and better performance. Whether you are a stock manager, financial analyst, or marketing manager, these formulas will allow you to build more modular and sustainable tables. The learning curve is moderate, but the gains in maintenance and speed are noticeable from the first complex files. Don’t hesitate to experiment and adapt these methods according to your specific needs!