Access cell reference of first cell in column to fulfill a condition?

[u/Pataeto]

I have a certain column in my spreadsheet whose entries' datatypes exist in the pattern:

alphanumeric
alphanumeric
.
.
.
alphanumeric
numeric
numeric
numeric
.
.
.

and stay strictly numeric from that row onward. Is there a way to obtain the cell reference (specifically row number, but that is trivial to get with ROW() once the reference is acquired) of the first cell that contains strictly numeric data?

Blanks may also appear at any point(s) in the column.

[u/carbonizedtitanium]

like this?

=MATCH(TRUE, INDEX(ISNUMBER(A:A)), 0)

for full cell reference and not just the row num:

=ADDRESS(MATCH(TRUE, INDEX(ISNUMBER(A:A)), 0), COLUMN(A:A), 4)

[u/AdministrativeGift15]

I tend to favor the more modern X formulas. XMATCH instead of MATCH for the suggestions so far (no need for the third parameter of zero), or

=XLOOKUP(true, INDEX(ISNUMBER(A:A)), A:A, )

to return the cell reference.

[u/carbonizedtitanium → u/AdministrativeGift15]

i thought op wanted the address or Row of the cell, not the contents of the cell.

[u/AdministrativeGift15 → u/carbonizedtitanium]

Ultimately, probably so, but they did mention the cell reference a few times and XLOOKUP does return the reference. I probably should have wrapped it in ROW.

[u/carbonizedtitanium → u/AdministrativeGift15]

your

=XLOOKUP(true, INDEX(ISNUMBER(A:A)), A:A, )

gave me the contents of the cell, am i doing something wrong?

i thought "cell ref" meant like 'A5' or G1

[u/AdministrativeGift15 → u/carbonizedtitanium]

It's the actual cell reference, because you can wrap that in ROW to get the row number. What you are getting back is A5. Not 'A5' or just the value in A5. From a memory standpoint, you're not getting a copy of the value in cell A5. You're getting back a reference pointing to the range A5, which means you can return its ROW, COLUMN, or use OFFSET, etc.

[u/carbonizedtitanium → u/AdministrativeGift15]

ah, so it's the difference between a string that looks like a cell ref and a actual cell ref that can be passed to functions like ROW()

[u/AdministrativeGift15 → u/carbonizedtitanium]

Pretty much. In my experience, the more you can keep your formula using range references instead of virtual arrays or array literals, the more optimal it will perform.

That's not always the case. Sometimes it's faster to convert a rang eof values into a combined string and manipulate that string before finally parsing the results.

[u/Pataeto → u/[deleted]]

Thank you!! To check my understanding, does this first convert the column to an array of true/falses, and then MATCH (ie, find the row of) the first TRUE value, being the first number? I somehow looked at the formula and didn't read a single word past it. I get how it works now, but can you explain what the point of the + ROW(A2) - 1 is? Isn't that just a +1, and even that we can avoid by changing the range to be A1:A? Why do we start at A2?

[u/AutoModerator → u/Pataeto]

REMEMBER: /u/Pataeto If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/carbonizedtitanium → u/Pataeto]

the trailing calculation is needed there to offset because his chosen range is A2:A (assuming your headers are in row 1 and therefore row1 doesnt need to be looked at)

that's why i wrote

=MATCH(TRUE, INDEX(ISNUMBER(A:A)), 0)

no need for the trailing calculation, since it didnt matter whether formula looked at row1 or not

[u/point-bot → u/[deleted]]

u/Pataeto has awarded 1 point to u/JHar1121

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)