Need to transpose game results to another sheet

Back with more for you smartypants moderators. Still haven't quite gotten the hang of array formulas, so that's probably going to be part of the solution here. I've got several different sheets going on in this statistics workbook, and am trying to record all the results on one sheet, and then another sheet breaks down the team-by-team comparisons. As far as I know, this is the final formula that I need for the document to run itself. To make matters worse, I'm pretty sure I had the solution earlier with some testing and for whatever godforsaken reason, I deleted it and forgot to save it for later...and of course, my future attempts have yielded nothing I need. So here goes, and it may help to look at the screenshots I took. Ignore any color schemes going on, those are conditionals for another purpose:

TeamResults.png (The sheet I'm working in)
Those various values in the lower-right portion are all manually entered, but it's gotten tedious for several hundred teams. So that's where this thread comes in.
Sweet16.jpg (The "Average Seed" sheet)

I need a formula that will search column-by-column for a certain team name AND distinguish between what seed they were when they won. Here's what I've gotten so far, and as usual, I've limited myself to a single column when I need it to cover the entire table.

=SUM(('Average Seed'!$DM$2:$DM$29=T(INDEX($Y$3:$Y$167, ROW()-2)))*('Average Seed'!$DL$2:$DL$29=VALUE(INDEX($AF$1:$AU$1, 1, COLUMN()-31))))

and then CMD-SHIFT-ENTER to create the array formula

So currently, if this were applied to the number of 1-seeds that Georgetown has, it would return a 2, even though there is still 1 more in the next section that isn't being evaluated (3rd from the bottom)

• 'Average Seed'!$DM$2:$DM$29 is the 2nd column in the Sweet16 pic that starts at Kentucky and goes down to Georgetown.
• 'Average Seed'!$DL$2:$DL$29 is the leftmost column in the Sweet16 pic. It is meant to label a seed with the corresponding team to the right.
These are the two that I need to be able to look over the entire table for matches.

• T(INDEX($Y$3:$Y$167,ROW()-2)) is my formula that uses the current cell and searches the index of teams for the one in the same row (in the TeamResults pic, the "2" under the 8-seed would be paired with "North Carolina".)
• $AF$1:$AU$1 is the row array on the top of the TeamResults pic from 1 to 16.

Sorry for the overly convoluted question.I just figured that you guys would benefit from as much detail as possible. I expect an overly complex but obvious formula full of IF-within-IF-within-IF statements to probably have something to do with the solution that I've just been overlooking. Thanks as always, you guys are a huge help for my mindless nerdiness.

Hi remiral.s,


Nice description... would it be possible for you to upload a sample file to enable forum to give a practical try.

Regards,
DILIPandey

<click on below 'star' if this helps>

Alright, posted a really small sample to the original post. See if you can recreate H3:W8 & AF3:AU8 on the Team Seeds sheet without it being manually entered values. Thanks so much. If you need reference to any of the formulas I used, they are on the 3rd sheet. The one in question that I've been trying to work with is the SUM formula at the top of that sheet.

Ok..

Can you share the calculation logic how H3 is 12?

Regards,
DILIPandey

<click on below 'star' if this helps>

Ah, my apologies. The 12 represents the number of times that the team in that row (Duke) is listed in the Sweet 16 results table ('Average Seed' sheet, cells D2:AK29) with a #1 seed. Duke is listed 20 times in the table, but only 12 of them have a '1' in the cell immediately to the left of the name. They are:

D8, D13, D14, D15, D22, D28, M3, V10, V12, V16, AE4, AE9

The idea I'm looking for is a formula that searches over that table and locates all the matches for a team (in this specific case, Duke) AND seed. So it would find 12 matches for [1] [Duke], 5 matches for [2] [Duke], 2 matches for [3] [Duke], etc.

So the formula that I am stuck at, adjusted from the first post in this case, would be

=SUM(('Average Seed'!$D$2:$D$29=T(INDEX($A$3:$A$8, ROW()-2)))*('Average Seed'!$C$2:$C$29=VALUE(INDEX($H$1:$W$1, 1, COLUMN()-7))))

So...the array formula searches D2:D29 in Average Seed for any matches to the Text of the team in A3:A8 that is in the same row as the cell w/ formula. It then searches for any values in Average Seed, cells C2:C29 for corresponding matches with a Value equal to the number in H1:W1 that is the same column as the cell w/ formula. Finally, it sums up how many TRUE-TRUE values it finds, and that's the return.


If it helps at all, I threw a reference row at the top of each column on 'Average Seed' with the possible seeds that can be found below the numbers given. It wasn't meant to be anything more than labels. But if you want to use it somehow, feel free to experiment. [1-8-9-16] [4-5-12-13] [3-6-11-14] or [2-7-10-15]

I'm still trying to tweak it, but my frustration has taken over, so I've had to walk away several times.

Hello remiral, try these

H3,

Please Login or Register  to view this content.

then copy down & across until W8,

AF3,

Please Login or Register  to view this content.

Then copy down & across until AU8