Match to find last row where a formula result is greater than zero length

[Jeffrey Mahoney]

I use match all the time in dynamic named ranges. Those ranges usually have values of alpha or numeric or both.

This time, I'm trying to find the last cell in a column where a value or a formula results in cell content length greater than zero.

I start off with a cell at the top of the column where a user can enter a lock set number. The lock set number populates down the column as the user fills in data in the form. The formula is checking to see if the user has filled out the row of values so it can put a lock number in that row. If the user hasn't reached that point, it results in a blank:
=IF(LEFT(UPPER(A1009),1)="I",LastLock(S$13:S1007),"")
LASTLOCK is a user defined function that i wrote to look at the cells provided and return the last non-empty cell. That UDF is slow and I prefer to use a formula if I can. Because there are a limited number of locks in a set, the user has the ability to overwrite the formula with a new lock set number in the column.

I have over a 1,000 rows in the form. I tried this formula, but it is catching all of the zero length (null) cells where there is a formula. So I always get the last row.
=MAX(IFERROR(MATCH("zzzzz",S$13:S1007),0),IFERROR(MATCH(1E+100,S$13:S1007),0))

I've searched the site and I'm at a loss.

Jeff

 

[Aladin Akyurek]

Assuming that S$13:S1007 is text and you want the last non-blank value:

=LOOKUP(9.99999999999999E+307,SEARCH("?*",S$13:S1007),S$13:S1007)

If you want the native row number of the last non-blank value:

=LOOKUP(9.99999999999999E+307,SEARCH("?*",S$13:S1007),ROW(S$13:S1007))

Hope this helps.

Just for the record: Look up value for the last text value including a formula blank is REPT("z",255) and lookup up value for the last numeric value is 9.99999999999999E+307. Both derive from Excel itself.

 

[Jeffrey Mahoney]

Thank you Aladin. I did want the value of the cell, not the row. But it is good to have the second one for future use. That is fantastic. It works.

On the lookup for last value: I was pretty sure my set of numbers wouldn't reach beyond a googol (1e+100). The only way I could grasp the scope of how large 9.99999999999999E+307 is, was to divide it by a googol. Maybe Excel 3016 will have several billions of rows with the ability to calculate the number of stars within 100 light years; then we'll need an extraordinarily large number like that in our Lookups.

Jeff

 

[Aladin Akyurek]

Thank you Aladin. I did want the value of the cell, not the row. But it is good to have the second one for future use. That is fantastic. It works.

Great.

On the lookup for last value: I was pretty sure my set of numbers wouldn't reach beyond a googol (1e+100). The only way I could grasp the scope of how large 9.99999999999999E+307 is, was to divide it by a googol. Maybe Excel 3016 will have several billions of rows with the ability to calculate the number of stars within 100 light years; then we'll need an extraordinarily large number like that in our Lookups.

Jeff

Nothing to do with google or "googol". Just a matter of anchoring in a standard in order to avoid whimsical variations. Here are some links:
https://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
https://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html