Forum Discussion
Formula Help
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
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))
=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.
- 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
- can you explain to me what the COLUMN(A1) function is doing or what its meant to be for?
=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.
