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

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.

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?

CodePudding user response：

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", )

CodePudding user response：

Try this formula in A15 and drag to the right:

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

CodePudding user response：

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.