If, Index & Match formula help required

[Steven Mihulka]

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"))

[#2]

Hi Steven,

Try exploring Indirect function which you can use here.

As you already know about complex formula, you can easily customize it to suit your need.  thanks.

Regards,

DILIPandey

[#3]

Dear Steven,

Try this:

=IF(P13="Tod",YOUR FIRST FORMULA,
IF(P13="Jim",YOUR SECOND FORMULA,
""))

[#4]

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)

[#5]

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"))

[#6]

Hi Steven, just out of interest how many cells do you have with this formula in ?

Is it quick to recalculate?

[#7]

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

Cheers :)

Regards,

DILIPandey

[#8]

Hi Steven, If you have Excel 2016 or are able to add Power Query in a older version the best option may be to create queries for the data in both tabs and merge them together with an added key column in each to indicate Tod or Jim. You could then add the merged query to a new data sheet as a table and point your formulas there or if you wanted to get a bit more advanced you could add it to a data model in power pivot.