Return column header by comparing multiple row values conditions

I have only used basic formulas in excel, and after reviewing many posts on this site I can't seem to figure out how to generate the output I am looking for. I understand the logic but I have no idea how to get the desired result. I apologize if my reasoning is not technical. I plan on deleting a team in column A or B based on game results. Then I have wins calculated in row 24.

I am trying to have D26 generate the column header (name) in row 2 of the winner for our weekly college pickem.

D26 basis
1. max value row 24.
2. If more than 1 cell in row 24 equals to max. Look at value in C22 to L22 and compare to remaining team in cell to A22 or B22. If more than one column is still equal continue to total point to determine tie breaker.
3. look and compare difference between B23 and C23 to L23 and return column header of lowest difference. If more than one column is still equal return all names and they can split the weekly payout.


I apologize if this not conventional reasoning but I only use simple functions but I am trying to learn more about functions, and how to utilize excel.

As per forum rules, would you please post the solution as well. Thanks.

Based on the your example workbook, what is the current correct answer? Explain the logic again, just to be sure. Thanks.

Thank you for helping me with my problem.

I attached a new version simulating all the game results. In this example cell D26 would return "Brian H" from cell G2 as the winner.
The tie breaker game is located in row 22 column A and B, with the winner in A22 as the loser was deleted from B22.

Logic for determining winner

1. who has the most wins (row 24)? Result: tie between 6 (D24,G24,H24,J24,K24,L24)therefore these columns go to 1st tie breaker.
2. 1st tie breaker. In the columns containing the max wins. Does only one column, or no column contain the winning team located in cell A22 or B22. If only one column matches the remaining value in A22 or B22 return column header as winner. If no columns or multiple columns go to 2nd tie breaker. Result: D22 eliminated, G22 H22 J22 K22 L22 remain go to 2nd tie breaker.
3. 2nd tie breaker. Of the remaining columns compare the difference between guessed total points (cells C23 to L23) to actual total points B23. The column with the smallest difference, positive or negative, is the winner. Return column header. If more than one column is still tied return multiple column headers.

I'll look at your post.

FYI, can you check the cell ref syntax in post #1? I corrected those, your post #5 reintroduces the incorrect syntax. If you don't mine fixing those, I'd appreciate it. The : is used in Excel to indicate a connection of beginning range and ending range. A cell reference doesn't use those.

A22
B22

A22:Z22 < a range
A:A < all cells in column A
1:4 < all cells in rows 1 thru 4

Thanks.

Ok, here you go, this is really 3 separate formulas all linked inside IF/THEN tests. I wish it wasn't such a monster, but it is.

This is an array formula, you enter it into cell D26:

=IF(COUNTIF(C24:L24,MAX(C24:L24))=1, INDEX($C$2:$L$2, MATCH(MAX(C24:L24), C24:L24, 0)), IF(COUNTIFS(C24:L24, MAX(C24:L24), C22:L22, A22&B22)=1, INDEX($C$2:$L$2,MATCH(MAX(C24:L24)&A22&B22, INDEX(C24:L24&C22:L22,0), 0)), LOOKUP(2, 1/((C24:L24=MAX(24:24))*(C22:L22=A22)*(B23-C23:L23)=MIN(IF((C24:L24=MAX(C24:L24)) *(C22:L22=A22), ABS(B23-(C23:L23))))), $C$2:$L$2)))

...confirm it by pressing CTRL-SHIFT-ENTER, you will see curly braces { } appear around the formula indicating an active array and the answer should appear.


Please remember to edit your post #5.

Originally Posted by aschom

If more than one column is still tied return multiple column headers.

It would be almost impossible to do that in one cell using formulas.......but you could do that in multiple cells:

Put this formula in D26

Formula:

Please Login or Register  to view this content.

confirm with CTRL+SHIFT+ENTER and copy across to G26 or further depending on the maximum possible ties there might be.

If there is only one winner after the second tie-break then that will put that winner in D26 and leave the other cells blank. If there are 2 or more winners it will list those in D26, E26 etc.

See attached example where I changed the values so that Matt and Dillon are tied - change C23 to 92 or 90 and see how that changes the result....

Aschom, compliments to you. Your scenario was one of the more perplexing that I've attempted in a long time.

Cheers.