Forum Discussion

Copper Contributor

Sep 05, 2021

Solved

Extract unique items from a list

Please help me, I want to use formula to extract numbers that has 4 or 5 at the beginning of a value. Thanks in advance. Example: 15677 16577 37588 46777 24689 57438 68495 SOLUTION: 4677...

Formulas and Functions

PeterBartholomew1

Sep 05, 2021

FANIMOF

Since you are sorting and filtering numbers you could use

= SORT(FILTER(numbers, (numbers<60000)*(numbers>=40000)))

in Excel 365 or

= IFERROR(
    SMALL(
      IF(
        (numbers<60000)*(numbers>=40000),
         numbers
      ), 
      1+ROW()-MIN(ROW(numbers))
    ),
  "")

in legacy systems.

FANIMOF

Copper Contributor

Sep 05, 2021

Thanks for prompt reply, however, it returns first value correctly but it just repeated the same value in the second cell etc as I drag the formular down

HansVogelaar

MVP

Sep 05, 2021

FANIMOF

Did you confirm it with Ctrl+Shift+Enter?