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. To our great frustration, VLOOKUP is used a lot more often than the INDEX and MATCH, while the latter are really much better. INDEX and MATCH are 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 the functions VLOOKUP, MATCH and INDEX, then we’ll tell you why we prefer to combine the latter two over the first.

VLOOKUP

With the 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. The format is VLOOKUP (C2; $F$2:$G$5;2; FALSE). 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 3rd 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, two paragraphs to explain the INDEX and MATCH and one for the VLOOKUP. Do you think it’s crazy that the VLOOKUP is more popular? If you don’t look at it in this way, but compare it to camping: that’s really nicer with an inner and an outer tent, even if it’s a little longer than a single roof tent. Take that effort! Why?

1) The numeric reference (2, in the example) indicating the column from which the VLOOKUP must obtain a result is a star. 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 have been the fourth one. You might even think of adjusting the number, but think about the future user! We keep repeating it.

2) The necessary value “FALSE” is very often forgotten in the VLOOKUP* If it is omitted or replaced by TRUE, the function “approximately” searches in a very special way, which you hardly ever need. 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. Then you create a separate column for the MATCH and always 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 very fast.

How PerfectXL can Help

What can PerfectXL do for you in this case? PerfectXL has a risk message called Approximate Lookup. It searches for VLOOKUP (or HLOOKUP) functions with TRUE as last variable or without final variable. This almost invariably goes wrong. Soon PerfectXL will also point you to any numerical reference in search functions. The more these can be prevented, the better it is.

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!​

N.B. For more information on this subject, read the blog by Felienne Hermans about why VLOOKUP is considered harmful.

Never miss a Guideline!