How to index and match multiple criteria without repeating results?

Hello! This is really difficult to explain so it's best to look at the sample worksheet. I'm attempting to match 2 sport team's head-to-head results from a list of results, but I can only figure out how to match the most recent game they played against each other.

These are the steps I'm hoping to recreate:

- Input Team1's name in I1, and the Team2's name in L1
- It should then find all the instances where in the results section, Team1 & Team2 appear together in columns B & D (In any order)
- It should return the most recent game first, then find the second to most recent game and return that in the following row and continue

The only way I know how to do this is to match Team1 & Team2 and return the most recent game, how can I make it continue down?

Please look at the attached sample so it hopefully makes more sense. On the left is a replica of how my results columns are formatted, and on the right is what I am trying to achieve.

Thank you all in advance!

Thank you so much that is absolutely perfect. I truly can not thank you enough, I would have never figured that out.

i need your help regarding sum if. I have bulk data in which we have recorded customer count per hour and i want to sum the count in two different date range. i.e

Date Hr1 Hr2 Hr3 Hr4 Hr5 Hr6 Hr7 Hr8 Hr9
13-08-01 226 239 94 25 60 82 68 70
13-08-02 211 178 74 14 76 86 89 73
13-08-03 220 167 71 23 80 84 97 73
13-08-04 210 187 141 21 78 79 76 61
13-08-05 237 210 141 11 7 65 87 72 57
13-08-06 255 233 150 12 19 109 102 82 79
13-08-07 388 332 276 121 12 81 93 86 90
13-08-08 221 166 143 23 68 225 240 179
13-08-09 191 184 146 6 24 119 191 244 147
13-08-10 231 151 113 6 41 99 165 216 189


please help how to sum.

i need your help regarding sum if. I have bulk data in which we have recorded customer count per hour and i want to sum the count in two different date range. i.e

Date Hr1 Hr2 Hr3 Hr4 Hr5 Hr6 Hr7 Hr8 Hr9
13-08-01 226 239 94 25 60 82 68 70
13-08-02 211 178 74 14 76 86 89 73
13-08-03 220 167 71 23 80 84 97 73
13-08-04 210 187 141 21 78 79 76 61
13-08-05 237 210 141 11 7 65 87 72 57
13-08-06 255 233 150 12 19 109 102 82 79
13-08-07 388 332 276 121 12 81 93 86 90
13-08-08 221 166 143 23 68 225 240 179
13-08-09 191 184 146 6 24 119 191 244 147
13-08-10 231 151 113 6 41 99 165 216 189


please help how to sum.

Back to the original topic for a moment, would it be possible to modify the formula to return games played with just 1 team against any opponent? I have tried by removing the references to team 2, and it just returns a blank cell due to an error, but I can't understand why that is.

=IFERROR(INDEX(B$2:B$7,SMALL(IF(($B$2:$B$7=$I$1)+($B$2:$B$7=$L$1),IF(($D$2:$D$7=$I$1)+($D$2:$D$7=$L$1),ROW($B$2:$B$7)-ROW($B$2)+1)),ROWS($B$2:B2))),"")

After I remove the reference to the second team and set it as an array formula it is an error, this is what I end up with:

=IFERROR(INDEX(B$2:B$7,SMALL(IF(($B$2:$B$7=$I$1),IF(($D$2:$D$7=$I$1),ROW($B$2:$B$7)-ROW($B$2)+1)),ROWS($B$2:B2))),"")

I'm a bit stumped on what else I could remove from it, thank you.

Ahh perfect, I had read the + as an 'and' statement which is why I left the two IF's in.

Thank you very much, again!