# 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.

## How do the functions work?

First, we’ll explain how the functions VLOOKUP, INDEX and MATCH 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).

## 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.

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.

Read more about the practical application of INDEX & MATCH in the article:

Dedupe in Excel: find and remove duplicate values

## How PerfectXL can help

What can PerfectXL Risk Eliminator 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.

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

• Avoid >70 types of risks
• Improvement suggestions
• Quality reports

#### PerfectXL Risk Eliminator walkthrough

On this page you will find a step-by-step walkthrough of the PerfectXL Risk Eliminator.

#### PerfectXL Risk Eliminator technical information

Here you will find information like system requirements, release notes, and answers to most questions about our software.

#### Pay attention to units and number formats

Excel horror stories are often related to accidentally changing, shifting or changing units or number formats.

#### Array functions are past times

Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, most people do not know about them, and do not understand them.

#### (Hidden) circular references in your Excel file

(Hidden) circular references occur when Excel tries to compute a result of a cell that’s already been visited during the calculation round. Excel doesn’t warn us of conditional circular references.

#### Avoid long formulas

Even Thomas Jefferson, 19th century president of the United States, remarked: “The most valuable of all talents is that of never using two words when one will do.”

#### Never use fixed numbers in formulas

The use of fixed numbers is a bad idea. A future user will not know where the number came from, and fixed numbers don’t change automatically, and thus might be overlooked when a change is made.

#### The dangers of hidden information

Do not hide anything except some sheets. Excel has too many attractive options that look good but may be risky in the long run.

#### Calculate formulas only once

You might need to run a calculation for inflation, the result of which is the needed in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation.

#### Do not merge cells

It is so tempting to merge cells in Excel so that they form a header above two or more columns. Yes, we must admit, it looks nice, but resist the temptation because it can be dangerous!

#### Don't neglect Excel errors

It’s a bad idea to leave standard Excel errors in your workbook. Take the time to clean up, because after a while, you no longer know whether you left a mistake consciously or if there is something wrong with your spreadsheet.

#### Customization

Do you wish to refine PerfectXL Risk Eliminator’s analysis to cater for your specific requirements? PerfectXL allows for full customization of your user interface.

#### Keep a formula close to its input

Place a formula close to its input variables. You reduce the chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else.

#### Risks & suggestions

To be able to trust a spreadsheet, a thorough check for errors and mistakes is essential. There are lots of different ways a simple mistake can destroy the validity of a spreadsheet.

#### Reports

Reporting is an integral part of Excel. Reports are generated in Excel, reports are built in Excel, and in many cases reports are a form of documentation required to properly use certain models in Excel.