SOLVED

Index Function Problem

MVP

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

4 Replies
best response confirmed by Doug_Robbins_Word_MVP (MVP)
Solution

@Doug_Robbins_Word_MVP 

 

The third parameter of MATCH() is not a range but a 0, 1 or -1.

 

Thanks, Detlef.

For some strange reason, the stand-alone MATCH functions were returning the expected result with the third parameter as shown.

However by deleting that parameter, it was then possible to incorporate the MATCH functions into the INDEX function and obtain the desired result.

@Doug_Robbins_Word_MVP 

Strange. Are you on pre-DA Excel? When perhaps implicit intersection works for the parameter in standalone function.

Hi Sergei,
I am using 64-bit Microsoft 365 Apps for enterprise Version 2203 (Build 15028.20201 Click-to-Run) Current Channel.
1 best response

Accepted Solutions
best response confirmed by Doug_Robbins_Word_MVP (MVP)
Solution

@Doug_Robbins_Word_MVP 

 

The third parameter of MATCH() is not a range but a 0, 1 or -1.

 

View solution in original post