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 other, however I want to combine the two formulas into one, so if the cell P13=Tod it searches the "Raw Data Tod" tab for info or if P13=Jim it searches the "Raw Data Jim" tab.
Can someone help please?
=VALUE(IFERROR(IF(P$14="Tod",
IF(INDEX('Raw Data Tod'!$L$2:$BT$2000,MATCH(1,('Raw Data Tod'!$L$2:$L$2000='Scorecard'!P$13)*('Raw Data Tod'!$S$2:$S$2000="Tod"),0),34)="Exceeding Expectations","5",
IF(INDEX('Raw Data Tod'!$L$2:$BT$2000,MATCH(1,('Raw Data Tod'!$L$2:$L$2000='Scorecard'!P$13)*('Raw Data Tod'!$S$2:$S$2000="Tod"),0),34)="Demonstrated Regularly","4",
IF(INDEX('Raw Data Tod'!$L$2:$BT$2000,MATCH(1,('Raw Data Tod'!$L$2:$L$2000='Scorecard'!P$13)*('Raw Data Tod'!$S$2:$S$2000="Tod"),0),34)="Average","3",
IF(INDEX('Raw Data Tod'!$L$2:$BT$2000,MATCH(1,('Raw Data Tod'!$L$2:$L$2000='Scorecard'!P$13)*('Raw Data Tod'!$S$2:$S$2000="Tod"),0),34)="Rarely Demonstrated","2",
IF(INDEX('Raw Data Tod'!$L$2:$BT$2000,MATCH(1,('Raw Data Tod'!$L$2:$L$2000='Scorecard'!P$13)*('Raw Data Tod'!$S$2:$S$2000="Tod"),0),34)="No Evidence","1",)))))),"0"))
=VALUE(IFERROR(IF(P$14="Jim",
IF(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)="Exceeding Expectations","5",
IF(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)="Demonstrated Regularly","4",
IF(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)="Average","3",
IF(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)="Rarely Demonstrated","2",
IF(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)="No Evidence","1",)))))),"0"))
