Prefer INDEX and MATCH over VLOOKUP

There are several ways to search in Excel. The VLOOKUP and the combination of INDEX and MATCH are well known, and to our great frustration, VLOOKUP is used a lot more often than INDEX and MATCH, while the latter combination is ultimately much better. The combination of INDEX and MATCH is less error prone and a lot more efficient.

Excel Guideline 11: Prefer INDEX and MATCH over VLOOKUP :: PerfectXL

How Do The Functions Work?

First, we’ll explain how the functions VLOOKUP, MATCH and INDEX work. Then we will go on to tell you why we prefer to combine the latter two and why we believe the use of the more commonly used VLOOKUP function should be reduced.

VLOOKUP

With VLOOKUP you search for the value in a certain column of a worksheet based on a selected value in the first column of the same table. This may sound a little complicated but essentially a set value is located in column one, and the matching value in column two is retrieved. The format is “=VLOOKUP(C2; $F$2:$G$5;2; FALSE),” where C2 is a reference to the value you are looking for in the first column, F2: G5 is the table you are searching in, the 2 means that you are looking for a result in the 2nd column of the table, FALSE means that you want to search exactly (and not approximately).

Excel Guideline 11: Prefer INDEX and MATCH over VLOOKUP - Example VLOOKUP :: PerfectXL

MATCH

On the other hand, the MATCH function determines where a certain value occurs in the range. The format is “=MATCH(C2;$F$2:$F$5;0)”. C2 is again the value you are looking for. F2: F5 is the range in which you are searching, the 0 means that you want to search exactly.

INDEX

With the INDEX function, you can retrieve a certain value from a column. The certain value is often determined by a MATCH. The format is INDEX ($G$2: $G$5; X) in which G2: G5 is the row from which you need the Xth (the MATCH) value.

Excel Guideline 11: Prefer INDEX and MATCH over VLOOKUP - Example INDEX and MATCH :: PerfectXL

Often INDEX and MATCH are put together in a cell and then you get INDEX (G2: G5; MATCH (C2; F2: F5;0)).

Prefer INDEX and MATCH

Ay, it takes two paragraphs to explain the INDEX and MATCH and only one for VLOOKUP. Do you think it’s crazy that VLOOKUP is more popular? Well don’t look at it in this way, instead compare it to camping which really is much nicer with an inner and an outer tent. Even if takes a little longer to set up than a single roof tent, most people realize it’s not the best solution and that the time. Take that effort! Here’s why it’s worth the effort:

1) The numeric reference (2, in the example) indicating the column from which the VLOOKUP must obtain a result is not flexible. If somebody ever inserts a column into the table – and this happens very often – the function no longer works, because it continues to take something from the third column while it should now be taking from the fourth one. You might think of adjusting the column number in the function, but think about the future user!

2) The necessary value “FALSE” is very often forgotten in VLOOKUP, if it is omitted or replaced by TRUE, the function “approximately” searches in a very special way, which is hardly ever needed/intended. In the MATCH function a value for exact search must also be entered (0), but for some reason it is less often forgotten.

3) For large tables and long chains of references, the INDEX and MATCH function can be much faster than the VLOOKUP. Especially if you need to find something several times based on the same match. In that case you can create a separate column for the MATCH and refer to that column in several INDEX formulas. Many users forget this, even if they are familiar with the INDEX and MATCH feature, but it makes the spreadsheet much more efficient.

How PerfectXL can Help

What can PerfectXL do for you in this scenario? Well one of the things the PerfectXL engine searches for is Approximate Lookup. It searches for VLOOKUP (or HLOOKUP) functions with TRUE as last variable or cases where the final variable is omitted. This almost invariably goes wrong, so we point users to these instances.

Excel Guideline 11: Prefer INDEX and MATCH over VLOOKUP - Example in PerfectXL :: PerfectXL

In short, it takes some getting used to, but do it anyway: use the INDEX and MATCH!​

Never miss a Guideline!