CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Canovice
Canovice

Reputation: 10501

In google sheets, grab last (by row) value in range that meets criteria

We have this dummy spreadsheet. In cell A15, we want the last value from A1:A13 that meets the criteria not equal to --, and not blank. A15 should then be 43, and B15 should be 23. Pretty much we just need the last non-blank, non "--" value in the range.

enter image description here

Presumably this can be done with a nasty long, nested ifelse type of statement, but we'd like to avoid that. Is there a better solution? Edit - added column C with integer range as I think this may be of some help?

Upvotes: 0

Views: 2518

Answers (3)

z..
z..

Reputation: 13156

You can enter this in A15 and drag to the right:

=regexextract(join("~",A1:A13),".*~(\d+)")

Edit: you can also do it with a single formula like this: =regexextract(regexreplace(substitute(join("#",query(A1:B13,,9^9))&"#"," ","~"),"[~-]+#","#"),regexreplace(regexreplace(substitute(join("#",query(A1:B13,,9^9))&"#"," ","~"),"[~-]+#","#"),"~(\d+)#","~($1)#"))

enter image description here

You can adjust the two occurrences of A1:B13 to cover a bigger range.

Upvotes: 0

Mike Steelson
Mike Steelson

Reputation: 15328

Try this formula in A15 and drag to the right:

=MATCH(9^9,A1:A13,1)

Upvotes: 0

player0
player0

Reputation: 1

try in A15:

=INDEX(INDIRECT("A"&MAX(ROW(A1:A13)*(A1:A13<>"")*(A1:A13<>"--"))))

alternative:

=QUERY(SORT(A1:A13, ROW(A1:A13), ), 
 "where Col1 is not null and Col1 <> '--' limit 1", )

Upvotes: 0

Related Questions