SOLVED

Formula Help

Brass Contributor

Hi everyone,  in need of some formula help.  in the attached spreadsheet, i am trying to fill the top 3 sales people in baseball sales hopefully using a formula.  You will see in row 5 i have baseball sales and the top 3 sales was: #1 Mike, #2 Jayden and #3 Luis.  In B25 i want excel to put Mike there automatically and C25 i want Jayden and D25 i want Luis name.

 

Any help would be awesome and much appreciated.

 

Thank you

7 Replies

@spalmer 

=INDEX($C$4:$M$4,MATCH(LARGE($C$5:$M$5,COLUMN(A1)),$C$5:$M$5,0))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

formula help.png

 

can you explain to me what the COLUMN(A1) function is doing or what its meant to be for?

@spalmer 

=LARGE(array, k)

Within the LARGE function k represents the k-th largest value.

In cell B25 for k the expression COLUMN(A1) evaluates to 1.

In cell C25 for k the expression COLUMN(B1) evaluates to 2.

In cell D25 for k the expression COLUMN(C1) evaluates to 3.

COLUMN(A1) allows to select the first, second, third-largest value when the formula is filled across the row.

its not working on my actual spreadsheet because the data is in different rows and columns then the spreadsheet i attached. the data on my actual spreadsheet starts on I21:T33. Where i would like the names inputted #1 is L45, #2 is M45 and #3 is N45. Thats why i was wondering what the COLUMN Function had to do with it

attached is where the actual data is on my actual spreadsheet.  Was just trying to give a simple version of it but it seems this column function needs to be exact.  thank you for helping me Oliver.  Hopefully this new attached file will be easier to see then me explaining it.  

 

thank you@OliverScheurich 

best response confirmed by spalmer (Brass Contributor)
Solution

@spalmer 

You are welcome. This formula works in the actual file.

 

=INDEX($J$21:$T$21,MATCH(LARGE($J$22:$T$22,COLUMN(A1)),$J$22:$T$22,0))

 

large.png

Thank you very much Sir, i figured out what was wrong. because i tried that exact formula but it was adding an extra column because i have those merged. so it was giving me J22:T23 instead of T22. geezzzz. haha.. well thank you again very much for your time Oliver
1 best response

Accepted Solutions
best response confirmed by spalmer (Brass Contributor)
Solution

@spalmer 

You are welcome. This formula works in the actual file.

 

=INDEX($J$21:$T$21,MATCH(LARGE($J$22:$T$22,COLUMN(A1)),$J$22:$T$22,0))

 

large.png

View solution in original post