Extract name of lowest values excel

Question

I have the following table:

I would like to extract using formulas or vba (if necessary) the name of the rows with less value (4 lowest for example). In this case the answer would be: Name 6, Name 7, Name 8 as they havel value = 0 and Name 4 as it is the first name with value = 1.

This is what I tried:

The problem is that the match formula cannot distinguish the correct row of the extracted value as there are varios names with the same value.

What version of Excel have you got? Also please edit the post to include your own attempt at solving this. — JvdV, Feb 05 '21 at 21:57
MS Excel version: Excel for Microsoft 365 MSO (16.013127.21962). Own attempt added too. — Alejandro, Feb 05 '21 at 22:10

score 3 · Answer 1 · answered Feb 05 '21 at 22:18

3

Right, with Microsoft365 you may try:

Formula in D1:

=INDEX(SORT(A2:B11,2,1),SEQUENCE(4))

answered Feb 05 '21 at 22:18

JvdV

70,606
8
39
70

Thaks for your help! Is there any alternative for MS Excel 2016? As it is the version I have in my work computer – Alejandro Feb 05 '21 at 22:31
Yeah probably but I closed the laptop down for the night. I'm sure someone else will help you out – JvdV Feb 05 '21 at 22:37

score 2 · Answer 2 · answered Feb 06 '21 at 01:11

The formulas below, since your example implies that you want items with a value less than or equal to the 4th lowest value, will return five (5) values since you have three zero's and two one's. The 4th lowest value is a 1. If you want to use some other algorithm to limit the return to just four values, please be more specific.

With O365

=FILTER($A$2:$A$11,($B$2:$B$11<=SMALL($B$2:$B$11,4)))

With XL2016

=INDEX($A$2:$A$11,AGGREGATE(15,6,1/($B$2:$B$11<=SMALL($B$2:$B$11,4))*ROW($B$2:$B$11),ROW(INDEX($A:$A,1):INDEX($A:$A,COUNTIF(B2:B11,"<="&SMALL(B2:B11,4)))))-1)

The -1 at the end is to compensate for indexing into an array starting at row 2 rather than row 1. Adjust for whatever row your data starts in

score 1 · Answer 3 · answered Feb 05 '21 at 23:18

There may be a better way but this is what I came up with.

=INDEX($A$2:$A$11,MOD(SMALL($B$2:$B$11+ROW($B$2:$B$11)/1000000,ROWS($D$2:D2)),1)*1000000-ROW($B$1))

You will probably need to use Ctrl+Shift+Enter to make it an array formula. I don't have Excel 2016 to test with.

QHarr · Answer 4 · 2021-02-06T09:26:01.780

So, for S&G I had a play with VBA. I decided I would do the ascending sort on the Value column first, then set desired target area value to the first N rows of Name column.

I restored the original order of Name column by storing it as a temporary custom order early on, and sorting with it at end. I checked custom order syntax against Steve Mallory's answer.

Option Explicit
Const N As Long = 4

Public Sub test()
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:B11")
    SortAndTopN rng
End Sub

Public Sub SortAndTopN(ByVal rng As Range)
    With rng
        .AutoFilter
        Application.AddCustomList ListArray:=.Columns(1).Offset(1, 0).Resize(.Rows.Count - 1, 1).Value
        With .Parent.AutoFilter.Sort
            .SortFields.Clear
            .SortFields.Add2 Key:=rng.Columns(2), SortOn:=0, Order:=1, DataOption:=0
            .Header = xlYes
            .Apply
            rng.Offset(0, rng.Columns.Count + 1).Resize(N, 1).Value = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1).Value
            .SortFields.Clear
            .SortFields.Add2 Key:=rng.Columns(1), SortOn:=0, Order:=1, CustomOrder:=Application.CustomListCount, DataOption:=0
            .Apply
        End With
        .AutoFilter
    End With
    Application.DeleteCustomList Application.CustomListCount
End Sub

Extract name of lowest values excel

4 Answers4