Finding nth Occurrence in multiple columns in Excel

Question

I have two columns with team names and two columns with corresponding stats. I need to go through the 2 columns and find the stats that match the team name, and they need to be in order. VLOOKUP, MATCH, and SEARCH don't seem to work with multiple columns. Does anyone know how this can be done? enter image description here

Does this need to be dynamic to allow for more data? Or will there always only be 2 values for each team? — BobbitWormJoe
– BobbitWormJoe, Commented Oct 29, 2014 at 7:56
Given that information, would it be possible for you to amend your example to make things clearer? At the moment, you have each team appearing precisely twice each. — XOR LX
– XOR LX, Commented Oct 29, 2014 at 9:28
so you want to dynamically populate the X - Home and X-Vis columns? — user3616725
– user3616725, Commented Oct 29, 2014 at 15:52
Sorry if my question wasn't clear. I want to populate the 4 columns on the right. So column 'A' would list each time it finds Team A and the corresponding stat 'X' — Adam12344
– Adam12344, Commented Oct 29, 2014 at 16:48

Community · Accepted Answer · 2018-05-08 21:49:10Z

1

Assuming in your picture, the "Home" title is in cell B2 then the following array formula can be put in the cells H3:L7 (array formulas need to be entered with Ctrl+Shift+Enter)

=IFERROR(OFFSET($D$1,-1+SMALL(IF(($B$3:$B$7=H$2)+($C$3:$C$7=H$2),ROW($B$3:$B$7),"X"),ROW()-ROW($2:$2)),--NOT((INDEX($B:$B,SMALL(IF(($B$3:$B$7=H$2)+($C$3:$C$7=H$2),ROW($B$3:$B$7),"X"),ROW()-ROW($2:$2)))=H$2))),"")

Let me break it down...

the logic is: OFFSET(top_of_results,row_number_that_has_Nth_team_score,0_or_1_for_home_or_away)
this is wrapped in an IFERROR in for where there isn't e.g. a 5th score for team A
using the array part IF(($B$3:$B$7=H$2)+($C$3:$C$7=H$2),ROW($B$3:$B$7),"X" we get an array of that has the ROW number if either (done using +) B or C have a value matching our team header (H$2) or an X otherwise
using SMALL(...,ROW()-ROW($2:$2)) we get the Nth smallest row, based on 1st being in row 3, 2nd in row 4 etc.
to get whether it is home or away, we check column B on our row to see if it matches --NOT((INDEX($B:$B,row_number_that_has_Nth_team_score-O)=H$2)) this gives 0 for home, 1 for away and this is used to offset the column

Hopefully it makes sense. Array formulas are very powerful, if a little confusing :-) I recommend CPearson's intro for more information.

Good luck!

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Oct 31, 2014 at 8:21

Captain

2,28817 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Captain Over a year ago

@adam12344 Did this work for you? Feel free to accept the answer!

Collectives™ on Stack Overflow

Finding nth Occurrence in multiple columns in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related