Lookup a maximum value

[Shoey68]

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

 

[jasonb75]

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]

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]

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.

 

[Shoey68]

Thanks Jason, works brilliantly. Superb!