Index match problem

[flammabubble]

Hi guys,

Working in google sheets, I'm trying to get an Index Match function to work with multiple criteria.

The sheet I am searching looks like this:

Game Number	Player	Match Type
1	Player1	Friendly
2	Player1	Friendly
3	Player1	League
4	Player1	Friendly
5	Player1	League
6	Player1	League
7	Player1	Friendly
8	Player1	League

I currently have a formula to return the 4th most recent Game Number (column A) as below:

=Index('Stat extract 1'!A:A,Match(9.99999999999999E+307,'Stat extract 1'!A:A)-3)

In this instance it would return "5" as intended, and I can adjust the -3 to change what output I am seeking.

What I want to do now is add in the additional criteria of only searching a specific match type. For example, I want to find the 4th most recent "League" match and therefore have the code return game number "3". I also want this to be conditional on whether D4 is not blank (which I believe would be done by adding =if(d4<>"" to the start). I would be grateful if anyone has any suggestions on how to do this?

 

[RoryA]

You could use

=LARGE(FILTER('Stat extract 1'!A:A,'Stat extract 1'!C:C="League"),4)

 

[flammabubble]

T

You could use

=LARGE(FILTER('Stat extract 1'!A:A,'Stat extract 1'!C:C="League"),4)

That's perfect, thank you!

 

[RoryA]

Glad to help.