How do I reference the three largest values in a column and their corresponding text in the column n-CodePudding

I am building an excel sheet that returns the three highest values from a column in another sheet (sheet2, column B) along with their corresponding company (sheet2, column a). Ultimately, in sheet 1, I want to have a table that will display the company with those values.

This is what I am trying to achieve: AWS ($280.9m), Google ($241.9m), Meta ($168.7m)

I was trying to use the large formula, but this does not help me with referencing the corresponding company so I’m unsure how to return both.

CodePudding user response：

You can use LARGE to get your top-n, and then wrap with INDEX/MATCH and OFFSET to get the company name.

Cell E3 formula:

=LARGE($B$2:$B$7,ROW(E1))

Cell D3 formula, which returns the column to the left of the Large value:

=OFFSET(INDEX($B$2:$B$7,MATCH(LARGE($B$2:$B$7,ROW(D1)),$B$2:$B$7,0)),,-1)

   or remove offset and use....

=INDEX($A$2:$A$7,MATCH(LARGE($B$2:$B$7,ROW(D1)),$B$2:$B$7,0))

Drag down your formulas as far as you would like.

CodePudding user response：

The above solution is good but kinda oldschool. I would use =SORT() function instead:

=INDEX(SORT(A46:B56;2;-1);{1;2;3})

Translation to human language:

=INDEX(SORT(MyArray;ColumnToSortBy;Descending);{get first three rows}, [column])

*note: depending on your windows settings your Array row separator may differ. The easiest way to check your it is to select any range with more than one row, then get to formula bar and click F9 to see result preview.

where [column] is an optional argument, by default it takes 1st column.