I am trying to write a formula where Cells A2:A16 are searched for numerical values and the results are output in cells C2:F2.

Within the data that will populate in A2:A16, there will only be 0-4 occurrences of a numerical value only (as seen below in column A).

For value 1 & 2 it will always be a number between 0-101 and for values 3 & 4 it will always be a number between 102-999. What is the best formula I can write for cells C2:F2 to get the results to populate like they do in yellow below?

Sheet showing A1:F16

1 Answer 1


Use filter() like this:

cell C2:

=transpose( filter(A2:A, isnumber(A2:A), A2:A <= 101) )


cell E2:

=transpose( filter(A2:A, A2:A > 101) )

The isnumber(A2:A) bit is required to skip empty cells whose value is considered zero and thus below 101.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.