highest second highest and third highest

  • Thread starter Thread starter ernie
  • Start date Start date
E

ernie

Hello..

I have one lists of number in A1:A7 and a list of words in B1:B7. I need to
find the highest, second highest and third highest value in these lists and
display the value and the word next to it in cell C1,C2, D1,D2 and E1,E2
respectively. However, there are same values for highest, second highest and
third highest in these list. If the value is the same, can I have it
separated?

For example: A1 B1
100 Car
100 Bus


Assuming both 100 is the highest. I want both Car and Bus to be shown in
cell C1. Possible ?Or any other solutions? Please kindly advise. Thank you.

help me
 
HELP ME! THANKS
--
help me









- Show quoted text -
Click to expand...

Ernie, be patient

This list always comes up with an answer, unless your request doesn't
make sense or there is not a logical solution!

Wait, your "help me" plea will probably be answered very soon!

Pete
 
The following formula will return the value in column B corresponding
to the highest value in A. In this example, the data is assumed to be
in A2:B8.

=INDEX(A2:B8,MATCH(LARGE(A2:A8,1),A2:A8,0),2)

For the second highest, use

=INDEX(A2:B8,MATCH(LARGE(A2:A8,2),A2:A8,0),2)

For the third highest, use

=INDEX(A2:B8,MATCH(LARGE(A2:A8,3),A2:A8,0),2)

Or, you could use a single array formula:

=INDEX(A2:B8,MATCH(LARGE(A2:A8,{1,2,3}),A2:A8,0),2)

Select the three cells in the same row in which you want to have the
results, type the following formula and press CTRL SHIFT ENTER

Note that the 1,2,3 is enclosed in curly braces, not parentheses. This
is an array formula, so you MUST press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it
later. If you do this properly, Excel will display the formula in the
formula bar enclosed in curly braces { }. You don't type in the
braces; Excel includes them automatically. The formula will not work
correctly if you do not use CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.

If you want the results to be in three cells on the same row, use the
formula above. If you want the results in cells in the same column
spanning multiple rows, either TRANSPOSE the values:

=TRANSPOSE(INDEX(A2:B8,MATCH(LARGE(A2:A8,{1,2,3}),A2:A8,0),2))

or change the commas that separate the 1,2,3 within the curly braces
to semicolons:

=INDEX(A2:B8,MATCH(LARGE(A2:A8,{1;2;3}),A2:A8,0),2)

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
sorry guys. I have tried your methods but it doesn't work. how do I paste the
VBA codes into my worksheet? p45cal ? Please kindly advise.
 
THANKS ALOT. I got exactly what I need from your formula and VBA code.

You are really good at this p45cal. Thanks again.
 
Back
Top