Adding Criteria to Index Match

[jharvey87]

Can anyone point out what I'm doing wrong.

This formula works fine:
=INDEX('Revenue Spread'!$F$8:$BP$10000,MATCH($D670,'Revenue Spread'!$H$8:$H$10000,0),MATCH(TEXT(BC$4,"Mmm-YYYY"),'Revenue Spread'!$F$7:$BP$7,0))


However, when I tried to add a third set of criteria, it does not work.

=INDEX('Revenue Spread'!$K$8:$BP$10000,MATCH($D670,'Revenue Spread'!$H$8:$H$10000,0),MATCH($F670,'Revenue Spread'!$J$8:$J$10000,0),MATCH(TEXT(BC$4,"Mmm-YYYY"),'Revenue Spread'!$K$7:$BP$7,0))

 

[jsb1921]

syntax of the command INDEX(array, row_num, [column_num])

=INDEX('Revenue Spread'!$K$8:$BP$10000 -- array
,MATCH($D670,'Revenue Spread'!$H$8:$H$10000,0) -- row
,MATCH($F670,'Revenue Spread'!$J$8:$J$10000,0) - col
,MATCH(TEXT(BC$4,"Mmm-YYYY"),'Revenue Spread'!$K$7:$BP$7,0)
)
Normally, the index takes 3 arguments, you are giving 4.
Is that intentional or by mistake or you have a special need

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=INDEX('Revenue Spread'!$K$8:$BP$10000,MATCH($D670&"|"&$F670,'Revenue Spread'!$H$8:$H$10000&"|"&'Revenue Spread'!$J$8:$J$10000,0),MATCH(TEXT(BC$4,"Mmm-YYYY"),'Revenue Spread'!$K$7:$BP$7,0))

May need array entry depending on you version.

 

[jharvey87]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=INDEX('Revenue Spread'!$K$8:$BP$10000,MATCH($D670&"|"&$F670,'Revenue Spread'!$H$8:$H$10000&"|"&'Revenue Spread'!$J$8:$J$10000,0),MATCH(TEXT(BC$4,"Mmm-YYYY"),'Revenue Spread'!$K$7:$BP$7,0))

May need array entry depending on you version.

That worked. thank you. I will update my information as suggested as well.

 

[Fluff]

Glad we could help & thanks for the feedback.