Forum Discussion

Brass Contributor

Dec 07, 2023

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

SALES.xlsx12 KB

Formulas and Functions

OliverScheurich
Dec 07, 2023
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))
SALES (1).xlsx13 KB

OliverScheurich
Gold Contributor
Dec 07, 2023
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.

SALES.xlsx12 KB
- spalmer
  Brass Contributor
  Dec 07, 2023
  can you explain to me what the COLUMN(A1) function is doing or what its meant to be for?
  - OliverScheurich
    Gold Contributor
    Dec 07, 2023
    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.
- spalmer
  Brass Contributor
  Dec 07, 2023
  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

Share

Resources