MrExcel Publishing
Your One Stop for Excel Tips & Solutions

EXTRACTING MORE THAN 1 LOWEST VALUE


Posted by Holans Rudi on February 21, 2001 7:34 AM

Does there exist a formula to extract the 3 lowest or highest values from a row. In addition, I also need to know the value (text) in the first row of the worksheet, right above the extracted value.
NAME A B C D E F
1 5 6 7 4 8 8

The result must be:
4 D 5 A 6 B

The function operates per row.

Many thanks for your support.

Rudi HOLANS


Posted by cpod on February 21, 2001 10:22 AM

Check Excel help for the small() and large() functions.

Posted by Aladin Akyurek on February 23, 2001 2:11 PM

Here is a formula-based solution.

Caveat: if your data contains duplicates and these show up in the subset of 3 lowest (highest) values, you will get the same text. Your example data have 3 highest values. What you get then will be:

8 E
8 E
7 C.

Here we go.

Assuming your labels occupy the range A1:F1 and your numeric values A2:F2.

Type

H1 =SMALL($A$2:$F$2,ROW()) [ copy down to H2:H3 to get 3 lowest values ]

G1 =INDIRECT(ADDRESS(1,COLUMN($A$2:$F$2)+MATCH(SMALL($A$2:$F$2,ROW()),$A$2:$F$2,0)-1)) [ copy down to G2:G3 to get labels corresponding to the 3 lowest values ]

Replace SMALL by LARGE to get 3 highest values and corresponding letters.

Hope this helps.

Aladin