CODEWITHSUNDEEP
vbaexcelexcel-formula
Michael Falciglia
Michael Falciglia

Reputation: 1046

How to display a value of a cell using the LARGE formula feature

In Excel using the LARGE function I have listed the top five values in the range of cells C2:C13 into the cells F2,F3,F4,F5,F6. I used the following formulas in the corresponding cells to do so:

Cell F2 I used this =LARGE(C2:C13,1)
Cell F3 I used this =LARGE(C2:C13,2)
Cell F4 I used this =LARGE(C2:C13,3)
Cell F5 I used this =LARGE(C2:C13,4)
Cell F6 I used this =LARGE(C2:C13,5)

Here is a screenshot:

Top five values example

However, what I would like to do is to display the value of the cells to the left of the five greatest values. Below is a screenshot of how I would like it to work if possible:

Desired results example

Upvotes: 1

Views: 253

Answers (2)

nwill001
nwill001

Reputation: 745

The following is tested in Google Spreadsheets.

This would be a simple solution:

 =MATCH(LARGE($C$2:$C$13,1),$C$2:$C$13)
 =MATCH(LARGE($C$2:$C$13,2),$C$2:$C$13)
 =MATCH(LARGE($C$2:$C$13,3),$C$2:$C$13)
 =MATCH(LARGE($C$2:$C$13,4),$C$2:$C$13)
 =MATCH(LARGE($C$2:$C$13,5),$C$2:$C$13)

Another more precise way:

=LOOKUP(LARGE($C$2:$C$13,1),$C$2:$C$13,$B$2:$B$13)
=LOOKUP(LARGE($C$2:$C$13,2),$C$2:$C$13,$B$2:$B$13)
=LOOKUP(LARGE($C$2:$C$13,3),$C$2:$C$13,$B$2:$B$13)
=LOOKUP(LARGE($C$2:$C$13,4),$C$2:$C$13,$B$2:$B$13)
=LOOKUP(LARGE($C$2:$C$13,5),$C$2:$C$13,$B$2:$B$13)

Upvotes: 1

pnuts
pnuts

Reputation: 59485

Please try: 

=INDEX(B:B,MATCH(LARGE(C$2:C$13,E2),C:C,0))

in F2 and copy down.

If purely for display purposes you might filter ColumnsB & C an apply a "Top 10..." selection for 5 items.

We could 'cheat' and manage without INDEX in the example provided but, for wider applicability, having chosen the top five values in ColumnF these are then MATCHed to their row numbers in ColumnC and the corresponding row number fed in to the INDEX function to determine the B value.

Upvotes: 1

Related Questions