Gameboy by Yokoi // PerfectXL Spreadsheet Validation

PerfectXL Risk Eliminator

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.

Prefer INDEX and MATCH over VLOOKUP // PerfectXL Risk Eliminator

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

VLOOKUP example // Prefer Index and Match over VLOOKUP // PerfectXL Risk Eliminator

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.

INDEX and MATCH // PerfectXL Risk Eliminator

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

Calculation doubts // Prefer INDEX and MATCH over VLOOKUP // PerfectXL Risk Eliminator

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

Read more about risks in Excel files

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

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

Our software inspects spreadsheets on all kinds of spreadsheet risk. Read more about some of the risks we detect in spreadsheets here.

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

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

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

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.

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

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.

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!

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.

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.

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.

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.

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.

Good, error-free spreadsheets are essential for using them reliably in business. This is why risk detection is an important aspect of spreadsheet validation.

Excel is a powerful tool. And when it can’t do what you need, VBA can fill the gap. Unfortunately, using VBA can lead to unpredictable behaviour, slow performance, and corrupt Excel files.

How can PerfectXL help you?

Share your questions with us, we are more than happy to help you. We will get back to you within 48 hours.