How to use INDEX-MATCH to replace VLOOKUP in Dashboards/Xcelsius

[former_member190855]

Often while designing dashboards we need to use a matching function in excel - in order to find a particular value (look up value) within an array.

Traditional excel users and experts tend to use VLOOkUP or HLOOKUP functions. However, when an XLF is converted to SWF, this function tends to slow down the dashboard performance. Ideal alternative is to this is to use filtered rows feature of the selectors. But it becomes tedious to use a selector for matching multiple values specially the lookup value itself is an array of values. The second best alternative is a combination of two functions - Match and then Index to achieve the same result. In this document, I will show you how to.

Say, for example, I have a dataset with Revenue for 10 countries and a second dataset with Orders for those countries. They are coming from different sources and for some imaginary reasons, they may not necessarily come in the same order of country and sometimes one dataset may not have all the countries as the other. You need to calculate and display Revenue/Order from these two datasets.

We are going to calculate the same in column L next to Orders column. To do that, we need to know the Revenue of Australia in row3 which is in a different row number in the Revenue dataset.

The first step is to determine the position or row number of Australia in Revenue set and we will use MATCH function. In M3 we will add the following formula

=MATCH(J3,$F$3:$F$12,0)

where J3 is the lookup value , Australia, in this case, finding its position in array F3:F12 which is the country list in Revenue dataset and 0 indicates exact match.  In the formula I have made absolute reference to range F3:F12 because I am going to the copy the formula to M4 through M12 to find match row# for all other countries in the list.

As you can see the result in column M, it returns the position of each country from the revenue dataset. For easier understanding and verification, I have added numbers next to the revenue dataset.

Next step is to determine the revenue based on the position which we will achieve by using INDEX function. In cell N3, we write the following formula

=INDEX($F$3:$G$12,M3,2)

where F3:G12 is the range of entire Revenue dataset, M3 is the position or row number within the range of the country whose revenue we are looking for and 2 is the column number in the range - which indicates to return the value of position 4 in revenue column.

Next calculate the Revenue/Order in column L with formula like =N3/K3 ( Ideally you should take care of situation of denominator=0)

For simplicity, I have broken down the formula in two steps. But you can also combine them as I have done in column O.

Here is a screenshot of only the formula

I hope you find this helpful and useful for your work. Please let me know your thoughts.

Happy dashboarding!