Forum Discussion

airoosh's avatar
airoosh
Copper Contributor
Aug 14, 2024

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

 

If say on the Consultant sheet - coordinator C1 is selected, I want the 'V with green fill' be populated on the matching dates:

 

FYI - column A in Consultant sheet is a dropdown of the list of coordinators in the Coordinator Sheet

 

I use this formula but its giving me the #N/A error:

 

=INDEX(Coordinators!$B$7:$CM$26,MATCH(Consultant!B11,Coordinators!$B$6:$CM$6,0),MATCH(Consultant!$A$12,Coordinators!$A$7:$A$26,0))

 

 

You're assistance is greatly appreciated!

 

 

excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Aug 14, 2024
    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.

Resources