INDEX MATCH multiple critera

Hey guys,

Thanks for your help. I have a row with 1 criteria (A), a row with another (B), and a third row with an output. I want to be able to change a cell that selects criteria A and it show the scores related to each of criteria B. I know I haven't explained that so well so have attached an example document.

For those who may not be able to access the file, on one page I will have:

Game - Game 1 Game 1 Game 1 Game 2 Game 2 Game 2
Player - Jerry Jimmy Phillip Jerry Jimmy Philip
Match Score - 9 8.5 7 6 6.5 9

I want to be able to select 'Game 1' and display:

Jerry - 9
Jimmy - 8.5
Phillip - 7

This is my formula at the moment: =IF($AY$16:$BD$16=$AX$9,INDEX($AY$18:$BD$18,MATCH(BA24,$AY$17:$BD$17,0)))
which works for Game 1, but if I change cell AX9 to 'Game 2' I get errors

Thanks

Callum
Newbie

Could you explain how the lookup looks for value of 1 but array has div/0 and 0 but it still works?

Thankyou. I assume that is why you divided rather than multiplied to turn the True and False into 1's and 0's?

This worked perfectly, thank you!!!!

players

Formula:

Please Login or Register  to view this content.

match score

Formula:

Please Login or Register  to view this content.

Hey,

I have a new slightly different problem.
I now have to match row 1 with x, row 2 with y, and column a with z to give the data in that cross reference. Is this possible?

Thank you, I have attached an example file

I propose such a formula.

Formula:

Please Login or Register  to view this content.

kvsrinivasamurthy & Czeslaw,

Both of those solutions worked perfectly, thats amazing! Thank you both very much!

Callum

New problem already!

Sorry!

Slightly different - I want to be able to add together any data that matches multiple criteria

Thanks

In the fomula
SUMIF(B1:P1=[1 OR 2] AND B2:P2=S2 AND A3:A7=S4
how
A3:A7=S4
is possible.