INDEX MATCH multiple critera

[callummcgrath]

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

[XOR LX]

Hi,

I assume each combination of Game and Player can only occur once, then?

If so, in F19:

=LOOKUP(1,0/((C$11:H$11=B$4)*(C$12:H$12=E19)),C$13:H$13)

and copy down.

Regards

[philaugust2004]

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

[XOR LX]

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

Sure. Providing the numerical values within the lookup_array are sorted (which, since LOOKUP has the useful property of ignoring errors, will be in this case), then, if the lookup_value is not found within the lookup_array, the last numerical value which is less than the lookup_value will be returned.

So, using the workbook provided by the OP, with Game 1 in cell B4, the formula in F21, i.e.:

=LOOKUP(1,0/((C$11:H$11=B$4)*(C$12:H$12=E21)),C$13:H$13)

resolves to:

=LOOKUP(1,{#DIV/0!,#DIV/0!,0,#DIV/0!,#DIV/0!,#DIV/0!},C$13:H$13)

and so the 0 occupying the third position in this array will be that considered by LOOKUP.

In this construction, since the lookup_array can only ever consist of zeroes and #DIV/0! errors, any value greater than zero will suffice for the choice of lookup_value.

Regards

[philaugust2004]

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

[XOR LX]

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

Actually division will render the FALSEs as #DIV/0!s, and hence be ignored by LOOKUP.

Cheers

[callummcgrath]

This worked perfectly, thank you!!!!

[XOR LX]

You're welcome!

[Czeslaw]

players

Please Login or Register  to view this content.

match score

Please Login or Register  to view this content.

[callummcgrath]

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

[Czeslaw]

I propose such a formula.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Try this. ARRAY formula in Q4 then drag down.
ARRAY formula should be confirmed with Ctrl+Shift+Enter keys .
=INDEX($B$3:$M$7,MATCH($P4,$A$3:$A$7,0),MATCH(Q$3&$P$1,$B$1:$M$1&$B$2:$M$2,0))

[callummcgrath]

kvsrinivasamurthy & Czeslaw,

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

Callum

[callummcgrath]

New problem already!

Sorry!

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

Thanks

[kvsrinivasamurthy]

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