Forum Discussion

Steven Mihulka's avatar
Steven Mihulka
Copper Contributor
Mar 22, 2017

If, Index & Match formula help required

I have a spreadsheet with 3 tabs, "Scorecard" used to pull in data from the other two tabs "Raw Data Tod" and "Raw Data Jim".   I have two formuals as per below that work independently of each othe...
Formulas & Functions
Need Help
Wyn Hopkins's avatar
Wyn Hopkins
MVP
Mar 23, 2017

Hi Steven

Any chance you can load a de-sensitised version of what your doing here?

There may well be a way of simplifying this formula significantly.

 

e.g. this part of the formula is repeated over and over, so would be better if possible to split it out into a helper column

 

 INDEX('Raw Data Jim'!$L$2:$BT$2000,MATCH(1,('Raw Data Jim'!$L$2:$L$2000='Scorecard'!P$13)*('Raw Data Jim'!$S$2:$S$2000="Jim"),0),34)

Steven Mihulka's avatar
Steven Mihulka
Copper Contributor
Mar 23, 2017

Hi everyone, thanks for the help, but I have managed to simplify the formual down to this, which works:

 

 

=VALUE(IFERROR(

IF(INDEX(INDIRECT("'Raw Data "&P$15&"'!$A$2:$BT$2000"),MATCH(1,(INDIRECT("'Raw Data "&P$15&"'!$BR$2:$BR$2000")=P$13)*(INDIRECT("'Raw Data "&P$15&"'!$E$2:$E$2000")=P$14),0),45)="Exceeding Expectations","5",

IF(INDEX(INDIRECT("'Raw Data "&P$15&"'!$A$2:$BT$2000"),MATCH(1,(INDIRECT("'Raw Data "&P$15&"'!$BR$2:$BR$2000")=P$13)*(INDIRECT("'Raw Data "&P$15&"'!$E$2:$E$2000")=P$14),0),45)="Demonstrated Regularly","4",

IF(INDEX(INDIRECT("'Raw Data "&P$15&"'!$A$2:$BT$2000"),MATCH(1,(INDIRECT("'Raw Data "&P$15&"'!$BR$2:$BR$2000")=P$13)*(INDIRECT("'Raw Data "&P$15&"'!$E$2:$E$2000")=P$14),0),45)="Average","3",

IF(INDEX(INDIRECT("'Raw Data "&P$15&"'!$A$2:$BT$2000"),MATCH(1,(INDIRECT("'Raw Data "&P$15&"'!$BR$2:$BR$2000")=P$13)*(INDIRECT("'Raw Data "&P$15&"'!$E$2:$E$2000")=P$14),0),45)="Rarely Demonstrated","2",

IF(INDEX(INDIRECT("'Raw Data "&P$15&"'!$A$2:$BT$2000"),MATCH(1,(INDIRECT("'Raw Data "&P$15&"'!$BR$2:$BR$2000")=P$13)*(INDIRECT("'Raw Data "&P$15&"'!$E$2:$E$2000")=P$14),0),45)="No Evidence","1"))))),"0"))

 

 

 

  • dilipandey's avatar
    dilipandey
    Copper Contributor
    Mar 24, 2017

    Nice to know that you managed to resolve it with "Indirect" function :)

     

    Cheers :)

     

     

    Regards,

    DILIPandey

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Mar 24, 2017
    Hi Steven, just out of interest how many cells do you have with this formula in ?

    Is it quick to recalculate?

Resources