Finding the first numbered cell in a specified range, then applying ArrayFormula to that formula in Google Sheets

1
votes

Using Google Sheets, I'm currently using this formula to find the first cell in a specified range that fulfills the ISNUMBER() condition:

=INDEX(FILTER(AC!D9:K9,ISNUMBER(AC!D9:K9)), 1)

This is working fine. However I need to apply this to an entire column, so one alternative would be dragging it down so subsequent cells contain a similar formula, for example the cell below this one would contain:

=INDEX(FILTER(AC!D10:K10,ISNUMBER(AC!D10:K10)), 1)

And this works too. But I'd like to find a way to make it cleaner and use ArrayFormula to apply it to the entire column, and the default Ctrl + Shfit + Enter formula:

=ArrayFormula(INDEX(FILTER(AC!D9:K9,ISNUMBER(AC!D9:K9)), 1))

Isn't working. Anyone know how I could make it work?

1
google-sheets
What's the purpose of INDEX? - TheMaster
@I'-'I to find the first value if the filtered array - CodeIntern

1 Answers

0
votes

index does not work with ArrayFormula

Please try:

=ArrayFormula(IFERROR(REGEXEXTRACT( TRANSPOSE(QUERY(TRANSPOSE(IFERROR( 1 * D9:K12)),,2^99)), "\d+"),"no number here"))

enter image description here

How it works

IFERROR( 1 * D9:K12)

will return numbers only from range D9:K12.

=TRANSPOSE(QUERY(TRANSPOSE(IFERROR( 1 *D9:K12)),,2^99))

gives an array of numbers delimited by space. If a row has no number, it will return spaces.

REGEXEXTRACT( expression , "\d+")

will find the first number from a string.

Edit

To convert numbers into number format :

=ArrayFormula(IFERROR(REGEXEXTRACT( TRANSPOSE(QUERY(TRANSPOSE(IFERROR(1*D9:K12)),,2^99)), "\d+") * 1,"no number here"))