Greetings – and thanks in advance!
I am SUCCESSFULLY using a wildcard value within a MATCH formula to locate the appropriate column and then retrieve a value:
=IF(LEN(E11)>0,INDEX(Consolidated!$C$1:$O$109,MATCH($E11,Consolidated!$C$1:$C$109,0),MATCH("*Incident*",Consolidated!$C$1:$O$1,0)),"")
When I substitute a Named Range for the Column Name lookup, e.g, Number_of_Incidents_Attended, I receive a #N/A result:
=IF(LEN(E11)>0,INDEX(Consolidated!$C$1:$O$109,MATCH($E11,Consolidated!$C$1:$C$109,0),MATCH(Number_of_Incidents_Attended,Consolidated!$C$1:$O$1,0)),"") ==> #N/A
As a sanity check, I used this formula, which evaluates to TRUE:
=Number_of_Incidents_Attended = "Number of incidents attended"
And... the value in the Column Header is exactly "Number of incidents attended", no trimming required. (Also verified by formula, in addition to eye-balling.)
Why are these formulas not working identically?!?!