Forum Discussion
If, Index & Match formula help required
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"))
Nice to know that you managed to resolve it with "Indirect" function :)
Cheers :)
Regards,
DILIPandey