How to use INDEX-MATCH to replace VLOOKUP in Dashboards/Xcelsius
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!
You can use the same combination to replace HLOOKUP as well.
Nice!! Will keep in mind for development work.
This article is on my new 10 Lessons you Must Learn to Master SAP Dashboard Development http://centigonknowledge.com/article/topdashboardfeatures
Nice 🙂 This solution can be comparable to lookup functions for huge data
Thanks for the like Swapnil. Hope you get to use it for your development.
Very nicely explained Runali..Will definitely keep this in mind during development.
Thanks Abhijit for your read & comment... Its indeed a handy formula to know when using Xcelsius... can be applied to numerous scenarios.
Hello Runali, Thanks for the detailed explanation. However,I have a doubt in the above scenario, if you have another column for example City ( which makes each country values in multiple rows). Then how can I use INDEX and MATCH combination to get the same result.
For example:
Country || City || Revenue
India || Hyderabad || 100
India || Delhi || 150
US || Chicago || 200
US || NewYork || 180
US || Vegas || 200
I want to get the following result by looking at value 'US' and project on my bar chart
US || Chicago || 200
US || NewYork || 180
US || Vegas || 200
I am not sure how can I do this with Index & Match. Any thoughts?
Hi Raghu
Thanks for your feedback & question.Your scenario should be addressed with Filtered Rows insertion type of selection if your requirement is in Xcelsius.
Thanks
Runali
Hello Runali, I don't know if I'm the only in this situation but the filtered rows is good but NOT in all cases. The filtered rows insertion type is only available in some list components.
Let said we try to filter rows (more then one) from a BUTTON selection. I have a dashboard with 12 buttons each when press need to filters somes cells...
!!!
Hi Patrick,
Typically don't like plugging products unless it does exactly what you are asking for...
Filtered Summary would solve this problem but it is a paid add-on. It does what you are describing out of the box and was designed for all of these cases: Filtered Summary
We have well over 1000 Xcelsius / SAP Dashboards developers using this solution now.
Nice Article. 🙂
When we have number of cells containing vlookup() then it is recommended to use or replace with Index() and match() functions. This combination improves so much of performance of dashboard.
Balu, thanks for your read and feedback. Regards, Runali
Runali,
I used this technique for one of my work and doesn't seem to get the correct output.
I am bringing data from Webi report using BIWS into dashboard.
My scenario as follows -
I am selecting a country and year and the dashboard should display a bar chart with current year export value and previous year export value for five parameters which vary by country each year.
Say my parameters are A, B, C,D,E. I select a country Ghana - 2008. Ghana for year 2008 has only 3 parameters A,B,C and for year 2007 it has 2 - A,B. I have hardcorded A,B,C,D,E in a cell and used index-match to select values for year 2008. But at the time of display the hardcorded A,B,C,D,E should appear as it is hardcorded on the x-axis which is not happening. Only A,B and C will appear for current year and previous year.
Not sure whether i am missing some thing.
Arun
Dear Really nice document !!
Keep sharing material like this !!
BR,
Abhi