LOOKUP...MATCH...INDEX...or a combination?????

  • Thread starter Thread starter emonroe1950
  • Start date Start date
E

emonroe1950

This is a problem that keeps recurring....I have created a worksheet that
automatically enters predetermined values as time goes by. It works well for
the most part. One problem though. Let's say I have column D. From row 5 to
298 there are If...then formulas that fill in as the months roll by. In row
299 I have the following formula:
=LOOKUP(9.99999999999999E+307,I5:I298)
This has the effect of returning the last value in that column. However,
since the majority of that column hasn't occurred yet, the If...then
formulas evaluate to "0". What I need is the last value in the column that
is not "0" value.
Any ideas out there?
 
Hi
one way: enter the following matrix formula (entered with
CTRL+SHIFT+ENTER)
=INDEX(A1:A10000,MAX(IF((A1:A10000<>"")*(A1:A10000<>0),ROW(A1:A10000)))
)
 
Your solution to my worksheet problem worked perfectly Frank. Usually I
would have to figure out WHY something works, but I will save that for
later. Thank you much.
 
Back
Top