• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Index and Match - multiple criteria

I have huge list of items from which I am trying to create a dashboard. I tried to use Index and Match - need help with multiple criteria and multiple range while using Index and Match formula. Any suggestions would be appreciated.

attached are the sample photos. Not sure how to upload the sample file here.

formula used is - =INDEX(INDEX(Backend!$G$2:$T$57,,MATCH(Dashboard!E$4,Backend!$G$1:$T$1,0)),MATCH(Dashboard!$B5,INDEX(Backend!$E$2:$E$57,,MATCH($C$2,Backend!$F$9:$F$57,0)),0))
 

Attachments

  • Dashboard.xlsx
    16 KB · Views: 28
  • sample dashboard view.JPG
    sample dashboard view.JPG
    109.3 KB · Views: 11
  • Sample.JPG
    Sample.JPG
    37.5 KB · Views: 7
Hi Sameer,

Adding another INDEX (in Chihiro's formula) at MATCH lookup_array, will makes it CSE free:

=INDEX(Backend!G$2:G$57,MATCH($B5&$C$2,INDEX(Backend!$E$2:$E$57&Backend!$F$2:$F$57,),0))

One more with SUMIFS:
=SUMIFS(Backend!G$2:G$57,Backend!$E$2:$E$57,$B5,Backend!$F$2:$F$57,$C$2)

Use custom format of:
General;General;"NA"

Display zeros as "NA"

Regards,
 
I actually changed it to this way using CSE - =INDEX(INDEX(Backend!$H$2:$V$57,MATCH($B5&$E$1,Backend!$E$2:$E$57&Backend!$F$2:$F$57,0),0),MATCH(Dashboard!E$4,Backend!$H$1:$V$1,0))
 
Back
Top