Blog

Don’t look up

Guy Hiddleston - January 13, 2020

At Artifax our expertise goes beyond how to input data into ArtifaxEvent, we love taking it back out and looking at it as well. Please always feel free to get in touch to discuss your reporting requirements. Below is a small tip for those Excel users who love their VLOOKUP functions.

VLOOKUP and HLOOKUP are both common formulas used in Excel and are great ways to find and return a value from a table based on a unique lookup value, for example, selecting a single item will look up the price.

There are, however, limitations to these functions when starting to work with larger sets of data as found in our Excel dashboard. In those cases, using Index Match as a function can be far better.

Don’t look up

Why is Index Match better?

• Lookup cannot look left from the search value in the table

• When Match is used in tables it’s more robust

• Data does not need to be sorted

• It avoids Arrays which means better performance

How to Index then Match rather than VLOOKUP or HLOOKUP

What is the index function?

This is used to return a value at a given position in a range or an array. This is especially useful as it allows for the match function to feed the position into the formula when a match is found. The index then returns the desired value.

Matching values

The Match part of the formula is simply looking at value to match, followed by a range to look within and finally a match criterion (Exact, Less Than or Greater Than)

Putting it all into a formula

First INDEX the table which has the data you want

=INDEX(lookup table,

Then add the MATCH function

• A – the value you are looking for

• B – the column you are looking for value A in

• C – the type of match (Exact, less than, more than)

=INDEX(lookup table,MATCH(A,B,C)

Finally, close off the formula by adding the column number which contains the value you would like to return. The whole formula should look a bit like the below. For an example of this working please download here the attached Excel file.

=INDEX(lookup table,MATCH(search value, lookup table column, match type),column number to return)

Guy Hiddleston's avatar

Author

Guy Hiddleston

Business finance graduate Guy is a PRINCE2® practitioner and, before joining Artifax, owned and managed two shops. In his spare time Guy enjoys walking or running in remote locations, sailing and snowboarding.

Comment

Keep in touch with the latest news