HELP: Returning a value in an index

[R Weeden]

I am comparing two columns of dates to check if there are dates in the same
row for both columns

" =IF(ISNUMBER(A3),IF(ISNUMBER(C3),C3,"Not Processed")) "

This returns the date in C3, IF A3 AND C3 are both entered, The text "Not
processed" IF A3 is entered and C3 is not entered,
OR False if A# is not entered.

This formula is in Column F. I then want to check the index of column F to
return the Date OR the Text of the last non blank value in column F

I am now using " =INDEX(F:F,MAX(IF(ISNUMBER(F1:F65535),ROW(F1:F65535)))) "

This will only return a valid value if it is a date. If it is the text it
ignores it.


How can I get the INDEX to return the value of either a date or the text,
and still ignore it if it is FALSE?

Bob Weeden

 

[Biff]

Hi!

Not real sure what you're after.

My best guess:

=LOOKUP(2,1/(F1:F65535<>FALSE),F:F)

This will return the last entry in the range that is not FALSE.

Biff

 

[Aladin Akyurek]

One of:

=IF(ISNUMBER(F65536)+ISTEXT(F65536),F65536,
INDEX(F1:F65535,
MAX(IF(ISNUMBER(F1:F65535)+ISTEXT(F1:F65535),ROW(F1:F65535)))))

which needs to be confirmed with control+shift+enter, not just with enter.

=IF(ISNUMBER(F65536)+ISTEXT(F65536),F65536,LOOKUP(2,1/(ISNUMBER(F1:F65535)+ISTEXT(F65535)),F1:F65535))

 

[R Weeden]

THAT was what I was after... Thanks

Hi!

Not real sure what you're after.

My best guess:

=LOOKUP(2,1/(F1:F65535<>FALSE),F:F)

This will return the last entry in the range that is not FALSE.

Biff