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.

Dataset1.png

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.

Match_Formula.png

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.

Index_Formula.png

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

/wp-content/uploads/2013/11/3rdformula_319365.png

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

/wp-content/uploads/2013/11/comnibed_formula_319366.png

Here is a screenshot of only the formula

Final.png

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

Happy dashboarding!

To report this post you need to login first.

15 Comments

You must be Logged on to comment or reply to a post.

    1. Runali Ghosh Post author

      Thanks Abhijit for your read & comment… Its indeed a handy formula to know when using Xcelsius… can be applied to numerous scenarios.

      (0) 
  1. Raghu Raman Nanduri

    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?

    (0) 
    1. Runali Ghosh Post author

      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

      (0) 
      1. Patrick Delage

        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…

        !!!

        (0) 
        1. Ryan Goodman

          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.

          (0) 
  2. balu b

    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.

    (0) 
  3. Arun Kumar

    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

    (0) 

Leave a Reply