INDEX MATCH with multiple AND and OR criteria

[marc01]

Hi all,

I am looking to extract information about a players tennis serve location based on whether certain criteria are met.

For example I would like to know the x coordinate location of the serve when Roger Federer is serving, AND the serve outcome is "1st serve made" OR "1st serve ace" AND the point was won by "Roger Federer".

So far I have managed to get the following formula to work:

=INDEX(Table1[[#All],[Serve location(x)]],MATCH("1st serve made",Table1[[#All],[Serve outcome]],0))

However, struggling to then add in the other criteria. I would like to add in (1) OR "1st serve ace" in the serve outcome array (2) AND "Roger Federer" was serving in the server array (3) AND "Roger Federer" in the point won by array.

The plan would then be to use this formula to pull out all of the relevant x coordinate (and I can adapt for the y coordinate) data from a match.
(at the moment I am achieving this through manually filtering a table and copying and pasting the relevant data to another sheet, however this is taking a long time and also prone to me making errors )

Please can someone help me?

Best,

Marc

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[marc01]

Thanks for getting back to me.

I have attached an excel workbook with 3 pages:
1. Example of the raw data table that I manually use the column filters to then copy and paste the relevant location data from

2. Location data page with the completed copy and pasted data

3. Blank location data page


Many thanks!


Marc

[Bo_Ry]

Please try at B4 copy to C4 and copy both down

=IFERROR(INDEX(RawData!N:N,AGGREGATE(15,6,ROW(Table1[Serve location(x)])/(Table1[[Serve outcome]:[Serve outcome]]={"1st serve made","1st serve ace"})/(Table1[[Server]:[Server]]=$A$2)/(Table1[[Point won by]:[Point won by]]=$A$2),ROWS(B$4:B4))),)

Set custom format to # to hide 0.

[marc01]

Thank you very much for your help - I have just quickly adapted the formula to see if it work for x and y coordinated when the point is lost and it works perfectly!

I wish I fully understood all that was going on in this formula, will try spend some time figuring it out in my mind.

Again thank you for your help it is much appreciated.

Marc

[Bo_Ry]

The key is
for "OR" we can use like this 2 formula below.

/(Table1[[Serve outcome]:[Serve outcome]]={"1st serve made","1st serve ace"})
or this
/((Table1[[Serve outcome]:[Serve outcome]]="1st serve made")+(Table1[[Serve outcome]:[Serve outcome]]="1st serve ace"))

for AND is just divided

/(Table1[[Server]:[Server]]=$A$2) /(Table1[[Point won by]:[Point won by]]=$A$2)

[[Point won by]:[Point won by]] double square bracket is for lock column in the table reference, it works the same as a dollar sign for a column; $A$1:$A$20

[marc01]

quick question....what is the significance of having the final function ROWS(B$4:B4) referencing the cells that the formula goes in to? Just that by accident I forgot to change this and it still returned the correct answer

[Bo_Ry]

At B4 ROWS(B$4:B4) = 1
At B5 ROWS(B$4:B5) = 2 and so on.

This gives running number start from 1 for the first row of formula and use for [k] in AGGREGATE(15 for small,6 ignore error, array, [k])

[marc01]

wow thanks for explaining all of this, helping it make sense to me.

[jason.b75]

..what is the significance of having the final function ROWS(B$4:B4) referencing the cells that the formula goes in to?

If you insert / delete rows / columns then referencing a different cell can cause the formula to fail, referencing the cell that the formula goes into prevents that.

[marc01]

Thank you Jason for this

[marc01]

Hi Bo,

Sorry to bother you again. I have been using the formula you provided me with and made alterations to it to now look at return coordinates (instead of serve) and it all appears to be working great so thank you again! In a new file I went to change the custom format to # however it was not there....I just wanted to check if you had to manually entered this as a custom format, or if have somehow deleted the one that was there?

Best wishes,

Marc

[JeteMc]

If # is not in the list of custom formats, select the cell(s) where you want to apply it then type # into the 'Type:' window of the Format Cells dialog box.
Let us know if you have any questions.