SOLVED

MVP

# Index Function Problem

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

# Re: Index Function Problem

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

# Re: Index Function Problem

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.

# Re: Index Function Problem

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

# Re: Index Function Problem

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