Find Value- End Up - Formula Help

[jputman]

I need a formula that will look in a specific column on the same row as it is currently on for a value. If there is no value in that cell, it will look at the cell above it in that column for the value... if no value, up another cell until it find a value.

Example: I have the value 200 in cell A10 and no data in cells A11:A15 (all are empty cells). In B15, I want to have a formula that looks in A15 first... if no data, then it basically does an END-UP to find the first cell that is not empty above it (which would be A10 in this case) and get the value from it.

** I need this to be a formula... I could write a VBA function to do this without problem. Also, it cannot be as simple as writing a bunch of nested IF statements, as the first value it comes to could be 100 rows above.

Hope this makes sense... and I hope someone has an idea! I would certainly appreciate it!

 

[mikerickson]

Something like the CSE formula

=INDEX(A:A,MAX(ROW(A1:A1000)*(LEN(A1:A1000)>0)),1)

which has to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)


If you want to restrict it to A15 or above, change A1:A1000 to A1:A15

If all your entries are text try the non-CSE formula
=INDEX(A:A,MATCH(REPT("z",255),A:A),1)
If they are all numbers, =INDEX(A:A,MATCH(9E+98,A:A),1)

If they are mixed numbers and text, the CSE would have to do.

 

[jputman]

This works great... I was on the right track, using the INDEX and MAX(ROW()) deal.. but just couldn't tie it all together in the end. The LEN made the difference!

I went with the CSE formula... and altered it slightly to pick up the current cell as the last possible cell in the arrays.. and added the appropriate $$ signs to it so that I could copy it down, as follows:

{=INDEX(A:A,MAX(ROW($A$1:$A1)*(LEN($A$1:$A1)>0)),1)}

...for the formula in cell B1.

Thanks for the help mike!

-jputman

 

[jputman]

Better yet... I can just make the formula not depend on putting it in a specific cell and then copying it down... just replace the second reference in it with an INDIRECT:

{=INDEX(A:A,MAX(ROW($A$1:INDIRECT(ADDRESS(ROW(),1)))*(LEN($A$1:INDIRECT(ADDRESS(ROW(),1)))>0)),1)}

...and now I do not have to explain how to implement it to the person that is actually creating the spreadsheet.

Thanks again for the help...

 

[mikerickson]

It might be more understandable to the other person if you used a named Range

Select B1 and define this name
Name: HereAndAbove
RefersTo: =$A$1:$A1

Then your formula becomes
{=INDEX(A:A,MAX(ROW(HereAndAbove)*(LEN(HereAndAbove))>0)),1)

That way, you avoid mixing the slow CSE formula with the volatile INDIRECT

 

[Aladin Akyurek]

Better yet... I can just make the formula not depend on putting it in a specific cell and then copying it down... just replace the second reference in it with an INDIRECT:

{=INDEX(A:A,MAX(ROW($A$1:INDIRECT(ADDRESS(ROW(),1)))*(LEN($A$1:INDIRECT(ADDRESS(ROW(),1)))>0)),1)}

...and now I do not have to explain how to implement it to the person that is actually creating the spreadsheet.

Thanks again for the help...

Just curious why you need such an expensive approach while, entered B5,

[1]

=LOOKUP(9.99999999999999E+307,A1:A15)

would give you the last numeric value,

[2]

=LOOKUP(REPT("z",255),A1:A15)

the last text value, including a formula blank, and

[3]

=LOOKUP(9.99999999999999E+307,SEARCH("*",A1:A15),A1:A15)

any non-error last value (text, number, truth value, etc.).

Note that (1) and (2) work on whole columns (rows) too. The latter [i.e., (3)] requires modification to do the same. BTW, 9.99999999999999E+307 is a well-known constant in Excel, which you can find in Excel's Help under "limits".

 

[jputman]

Love it... even better so it doesn't have to be an array formula even with mixed text/numbers. I had known about the constant, but never would I have thought to use it in this manner...

thanks!

-jputman