Index with 3 match criteria

[Jamie Wagler]

Please refer to the attached file, Index3Match tab. I am trying to pull a value from the list in the green field by using a mix of values in the 2 left columns and row (lists in the orange cells)

I was trying an Index(Match(Match but am unsure of how to tie in the 3rd Match.

Thanks

 

[PaulF]

G13 (Array Formula)

=INDEX($C$2:$P$9,MATCH(A13&C13,A2:A9&B2:B9,0),MATCH(E13,D1: P1,0))

CSE

 

[Chihiro]

See attached. Note that it's array formula confirmed with Ctrl + Shift + Enter.

Edit: Doh. PaulF beat me to it.

 

[PaulF]

rggg... the above formula has : P1 << no space between the : and P and it keeps making it an emoticon

 

[PaulF]

Chihiro - Same answers same time

 

[Jamie Wagler]

Awesome Thanks!!!

 

[bosco_yip]

G13 (Array Formula)

=INDEX($C$2:$P$9,MATCH(A13&C13,A2:A9&B2:B9,0),MATCH(E13,D1: P1,0))

CSE

1] I think the above array formula is a typo, and should read as ….

=INDEX($C$2:$P$9,MATCH(A13&C13,A2:A9&B2:B9,0),MATCH(E13,C1: P1,0))

2] And, try this non-array option :

=SUMIFS(INDEX(C2: D9,0,MATCH(E13,C1: P1,0)),A2:A9,A13,B2:B9,C13)

Regards
Bosco

 

[Khalid NGO]

Hi,

2 more, No CSE:

=SUMPRODUCT(C2: P9,(A2:A9=A13)*(B2:B9=C13)*(C1: P1=E13))

=INDEX(C2: P9,MATCH(A13&C13,INDEX(A2:A9&B2:B9,),0),MATCH(E13,C1: P1,0))

Regards,

 

[bosco_yip]

Based on the OP's Input Table layout,

another simply non CSE formula used in G13 :

=INDEX(C2: P9,MATCH(A13,A2:A9,0)+MATCH(C13,B2:B3,0)-1,MATCH(E13,C1: P1,0))

Regards
Bosco