# Excel - INDEX MATCH query

Hoping someone can help me!

I've worked myself out a cricket scoring spreadsheet, which works absolutely fine - trying to sort out a summary page, where you see (funnily enough!) a summary of the game info.

As part of this, I'd like to be able to use the information on the following table:

``````Bowler  Wkts    Runs
Amir     0     41

Irfan    1     46

Sami     2     23

Afridi   2     40
``````

to select the best bowler for the summary page.

I have an understanding of writing an INDEX...MATCH... statement, but am having trouble with this, because I would like to select the bowler name where the wickets value is the most, but the runs value is the lowest amongst the bowlers taking that number of wickets - in this case, Sami.

I imagine I'll be trying something using INDEX, MATCH, MAX and MIN (with some IFs thrown in!), but really don't know where to start.

Any help would be gratefully received!

``````=INDEX(A2:A6,MATCH(MIN(IF(B2:B6=MAX(B2:B6),C2:C6,9E+109)),C2:C6,0))
``````

Confirm as an array formula with ctrl+shift+enter.

`MIN(IF(B2:B6=MAX(B2:B6),C2:C6,9E+109))` Finds the bowlers with the highest number of wickets and returns their number of runs. For everyone else, it simply returns an unrealistically high number (9E+109).

`MATCH(MIN(...,C2:C6,0)`

Uses `match()` and `min()` to find the lowest number of runs amongst the bowlers we previously identified.

`INDEX(A2:A6,...)`

Returns the actual bowlers name.

• Thanks for this, Kyle - as Norman Stanley Fletcher would have said, "it worked like a bleedin' charm!". Is there any way this could be extended to show the second and third best bowlers? – Dave Coram Mar 23 '16 at 16:21
• @DaveCoram Not easily. The second and third best bowlers in your example are obvious, but what if there was another bowler with 1 wicket? In order to find the third best bowler, we'd have to find the second most wickets, and the least runs with that amount of wickets. You'd be better off with a VBA solution. – Kyle Mar 23 '16 at 16:37