Returning 2 largest values from a list including absolutes

**ranj** · 07-24-2009, 02:52 PM

I have a small range of numbers containing both positive and negative numbers. ie

-3.21
4.00
2.33
-5.45

I need to return the 2 'largest' absolute numbers in their original format - so in this example, I need to get:

-5.45
4.00

Probably a really simple one, but I can't get my head around it! Thanks in advance.

**Paul** · 07-24-2009, 03:14 PM

Hi Ranj,

Perhaps this array formula will work for you. Adjust your range (A1:A4) to meet your needs. Also, to get the second 'largest', replace the red 1's with 2's.

Remember: When confirming an array formula (after typing or editing it) you must press CTRL+SHIFT+ENTER, not just ENTER. When done correctly Excel will automatically insert braces around your formula.

=IF(LARGE(ABS(A1:A4),1)=SMALL(A1:A4,1)*-1,LARGE(ABS(A1:A4),1)*-1,LARGE(ABS(A1:A4),1))

**ranj** · 07-24-2009, 03:51 PM

Many thanks Paul, works a treat! Excellent

**ranj** · 07-24-2009, 06:50 PM

I want to go one step further and now lookup the column header which the 2 largest numbers appear in. One of my worksheet contains:

Header 1 Header 2 Header 3 Header 4
-2.32 -5.00 4.00 1.00
<more rows of data>
..
<..>

Paul helped me to extract the top 2 values taking into account the absolute values. I have therefore put these 2 values into each row on a new worksheet. In this case:

-5.00 4.00

What I would like to do is now also get to the Header name which the top 2 values appear in. So I need to get to:

Header 2 Header 3

As far as I'm aware a HLOOKUP doesn't work in this case as I'm looking 'up' a column.

Much appreciated for any help.