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