HELP: Returning a value in an index
 

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:F6553 5)))) "

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

Returning a value in an index
 

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

"R Weeden" wrote in message
...
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:F6553 5))))
"

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

HELP: Returning a value in an index
 

One of:

=IF(ISNUMBER(F65536)+ISTEXT(F65536),F65536,
INDEX(F1:F65535,
MAX(IF(ISNUMBER(F1:F65535)+ISTEXT(F1:F65535),ROW(F 1: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 wrote:
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:F6553 5)))) "

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

Returning a value in an index
 

THAT was what I was after... Thanks


"Biff" wrote in message
...
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

"R Weeden" wrote in message
...
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:F6553 5))))
"

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