Get rid of "False" from an array

I doubt the validity of your formula because the size of the two ranges in your multiplication isn't likely to be the same when you exit laboratory conditions. However, even if that isn't a real problem or it can be overcome the result of that test must include False values which can't be removed. Please try the formula below instead.

=FILTER(Data,ISNUMBER(Data))

Data is a named range that I used for the purpose of transparency. In my test it was set to A2:A17. You can either name the range you are examining as I did or repalce the name in the formula with the range address, like, =FILTER($A$2:$A$17,ISNUMBER($A$2:$A$17))

Edit:-  =================================

If you don't have Excel 365 and must therefore do without the FILTER function, hopefully the function below will help. I have no way of testing but I believe all functions used in the formula are of older vintage.

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(Data)/(ISNUMBER(Data)),ROW(1:1))),"")

Paste this formula in a cell in row 2 or lower and copy down for as far as it returns a value. The Data range can be replaced with ard cell references as explained above. In this context please note that A:A must specify the column in which Data is located.

Maki

Please find attached a file with a User Defined Function (UDF) to do what I think you want. This follows my discussion point (in the answer from @Variatus).

The UDF is entered in the spreadsheet like this (see cell C2 in yellow):

=ArrayTest(A2:A17,"a",1)

...where ArrayTest is the name of the UDF with parameters:

• A2:A17 is the range you want to search,
• "a" is what to search for (needs inverted commas if text but a number can be entered without, e.g.6). These two parameters alone would produce the row numbers of the searched value BUT...
• the 1 is an optional input (lessRow in the VBA) if you want the result to be relative to a starting row- it must be an integer (like a row number) and gets subtracted from the row position (so I entered 1 to make row 2= position 1 as your question implied).

The output of the UDF is a text string in the cell like 'a' in positions 1; 3; 6; 10; 13;.

The VBA code for the UDF is just this (in Module 1):

Function ArrayTest(ByVal aRange As Range, aTest As Variant, lessRow As Integer)

For Each aCell In aRange
    If aCell.Value = aTest Then ArrayTest = ArrayTest & (aCell.Row - lessRow) & "; "
Next aCell

ArrayTest = "'" & aTest & "' in positions " & ArrayTest

End Function