Lookup a maximum value

Shoey68

New Member
Joined
Nov 21, 2010
Messages
15
Hi all,

I have three columns of data - I need to be able to look up the 4 highest values in column B and return the corresponding text from column A and monetary value from C(in different cells).

Can anyone help?

Sample data:

A1:A3
Red
Blue
Green

B1:B3
2
5
3

C1:C3
£100
£300
£700

In this example, I would need a formula to return 5(in say E7), Blue(in F7) & £300(in G7)

Apologies if I haven't explained too well!

Thanks in advance
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,909
Office Version
  1. 365
Platform
  1. Windows
Assuming unique values in column B, duplictes would need a different approach.

In E7 =LARGE($B$1:$B$3,ROWS($E$7:$E7))
In F7 =INDEX($A$1:$A$3,E7)
In G7 =INDEX($C$1:$C$3,E7)

Copying these down to E8:G8 would return the dta for the 2nd highest value, E9:G9 the third, etc.
 

Shoey68

New Member
Joined
Nov 21, 2010
Messages
15
Thanks Jason, It kinda worked - It returns the correct number in E7, but I get a #REF error in the other two cells.

Also (sorry!) the data in B may not be unique, I need to return just the highest 4 (unique or not) - If there are more than 4 identical values - Any four would do.

For example:

1, 3, 2, 3, 3, 4, 5, 3

I'd only need: 5, 4, 3, 3

Thanks again
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,909
Office Version
  1. 365
Platform
  1. Windows
Sorry, that was my mistake, E7 should have been MATCH(E7,$B$1:$B$3,0) in the 2 formula that returned errors, but that still wouldn't allow for identical values.

Instead try,

D1 =COUNTIF(B$1:B$3,">"&B1)+COUNTIF(B$1:B1,B1)

E7 =INDEX($B$1:$B$3,MATCH(ROWS(E$7:E7),$D$1:$D$3,0))
F7 =INDEX($A$1:$A$3,MATCH(ROWS(F$7:F7),$D$1:$D$3,0))
G7 =INDEX($C$1:$C$3,MATCH(ROWS(G$7:G7),$D$1:$D$3,0))

The formula in D1 when filled down will give a unique value to each entry, in the event of duplicates, the lowest row number will be returned first.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,315
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top