google-sheetsgoogle-sheets-formula

Excel getting names from biggest values with Regextract

I have a small problem with my Excel project and I can't find way how to make it true.

Current formula:

=INDEX(A2:A51; MATCH(LARGE(Value(Iferror(REGEXEXTRACT(split(C2:C51; "-"); "[0-9]+"))); 1) C2:C51; 0))

I want to get names from A column, but I want get the 3 biggest numbers, for example 7 or 13 from C column. If it is text, I want to get 0.

enter image description here

Changes many codes and still get errors.

edit: this is demo https://docs.google.com/spreadsheets/d/1vCvMjs28adVSmy80p4BqHokr_PUMxDupalSfLwXGhMk/edit?gid=2100307022#gid=2100307022

Solution

  • You can try this formula:

    =LET(x; SORT(FILTER(A2:F7;C2:C7 <> "No Progress");3;TRUE); y; BYROW(CHOOSECOLS(x;3); LAMBDA(r;SPLIT(r;" "))); CHOOSECOLS(CHOOSEROWS(SORT(HSTACK(x;y);8;FALSE);{1;2;3});{1;3}))

    Sample Output:

    Output

    Reference:

    Filter

    Sort