Forum Discussion
Help on formula
I have two sheets. The Coordinator sheet is like a master file and cell B7 to CM26 has a conditional formatting that I want to populate on the Consultant sheet. Coordinator sheet - master file ...
I think the problem is you have row and column backwards. but you also reference rows 11 and 12 on the consultant sheet when they appear to be 25 and 26 in the image. Based on the IMAGE I think the formula in B26 should be:
=INDEX(Coordinators!$B$7:$CM$26,MATCH(Consultant!$A26,Coordinators!$A$7:$A$26,0),MATCH(Consultant!B$25,Coordinators!$B$6:$CM$6,0))
and copy/fill right & down as needed.
That said you could use the new dynamic array formulas much easier and cell B26 would be
=FILTER(Coordinators!$B$7:$CM$26, $A26=Coordinators!$A$7:$A$26,"")
and fill down if needed.
=INDEX(Coordinators!$B$7:$CM$26,MATCH(Consultant!$A26,Coordinators!$A$7:$A$26,0),MATCH(Consultant!B$25,Coordinators!$B$6:$CM$6,0))
and copy/fill right & down as needed.
That said you could use the new dynamic array formulas much easier and cell B26 would be
=FILTER(Coordinators!$B$7:$CM$26, $A26=Coordinators!$A$7:$A$26,"")
and fill down if needed.