Apr 23 2022 12:52 AM
The following formula returns the required result
=INDEX(Archetypes!$G$3:$I$11,$G$7,$G$5)
where the value in G7 is provided by:
=MATCH($C$7,Archetypes!$E$3:$E$11,Archetypes!$F$3:$F$11)
and the value in G5 is provided by:
=MATCH($C$5,Archetypes!$G$1:$I$1,Archetypes!$G$2:$I$2)
Both of the following also return the required result:
=INDEX(Archetypes!$G$3:$I$11,MATCH($C$7,Archetypes!$E$3:$E$11,Archetypes!$F$3:$F$11),$G$5)
=INDEX(Archetypes!$G$3:$I$11,$G$7,MATCH($C$5,Archetypes!$G$1:$I$1,Archetypes!$G$2:$I$2))
However. the following results in #REF!
=INDEX(Archetypes!$G$3:$I$11,MATCH($C$7,Archetypes!$E$3:$E$11,Archetypes!$F$3:$F$11),MATCH($C$5,Archetypes!$G$1:$I$1,Archetypes!$G$2:$I$2))
Is it not possible to use a MATCH function to supply both the column number and row number for the INDEX function
Apr 23 2022 12:58 AM - edited Apr 23 2022 12:59 AM
SolutionApr 23 2022 03:15 AM
Apr 23 2022 03:24 AM
Strange. Are you on pre-DA Excel? When perhaps implicit intersection works for the parameter in standalone function.
Apr 23 2022 04:52 AM
Apr 23 2022 12:58 AM - edited Apr 23 2022 12:59 AM
Solution
The third parameter of MATCH() is not a range but a 0, 1 or -1.