LOOKUP Formula

[Snake Eyes]

I have cell F93 that has the following code.

=IFERROR(LOOKUP(2,1/(F28:F82<>0),F28:F82),"")

The intention is to display the date from a cell in the range of F28:F82 that contains the most recent (date) entry.
For example, if F37 contains 2-15-23, F43 contains 1-5-23 and F81 contains 3-1-23, cell F93 should display 3-1-23.

In other words, F93 should display the contents of the last cell with any value within F18:F82 regardless of the contents of any cell above that last cell displaying data.

My issue is that I have 6 cells in that range that have the same formula (see below.) These cells prevent the formula in F93 from displaying the contents of a cell in the range.

For example, F81 contains 3-1-23 and F30, F40, F50, F60, F70, F80 contain the formula below.
F93 is blank.
If I clear the formula from the 6 cells within F28:F82,
F93 displays 3-1-23.

How do I modify my formula in F93 to resolve this?

In case it matters, this formula is in 6 cells with the range F28:F82

=IF(AND(E55="",E56<>"",F55=""),"Expiration Date ?",IF(AND(E55="",E56="",F55=""),"",IF(AND(E55="",F55<>"",E56<>""),"Original Expiration",IF(AND(E55="",F55<>""),"Expiration Date",IF(AND(E55<>"",E56<>"",F55=""),"Expiration Date ?",IF(AND(E55<>"",F55=""),"Expiration Date",IF(AND(E55<>"",F55<>"",E56<>""),"Original Expiration",IF(AND(E55<>"",F55<>"",E56=""),"Expiration Date",IF(AND(E55<>"",F55<>""),"")))))))))

Thank you.

 

[Fluff]

How about

=IFERROR(LOOKUP(2,1/(F28:F82<>""),F28:F82),"")

 

[Snake Eyes]

Fluff,
That works but only if I copy Col F from the original sheet and paste it into a blank worksheet.
In the original sheet F93 is still blank. I'm trying to figure out why.

 

[Fluff]

Remove the iferror & what do you get?

 

[Snake Eyes]

=LOOKUP(2,1/(F28:F82<>""),F28:F82)

is still blank.
I also copied the range to another column in the same worksheet and AA93 is blank as well.

 

[Fluff]

If it's still blank it sound as though you might have cell that contain a space

 

[Snake Eyes]

Possible however, if that were the case, a copy would behave the same regardless of where it is pasted.
A copy pasted in the same worksheet, F93 is blank.
A copy pasted in a new worksheet, in the same workbook, F93 works as desired.

 

[Fluff]

Without your actual sheet there is not much I can do. The formula will find the last cell that is not empty and does not contain "".

 

[Snake Eyes]

Fluff,
I was able to resolve the issue. Original and working code is below.
I'm not exactly sure why it fixed the issue but it did.

Original Code in 10 of the cells in the LOOKUP range:

=IF(D70="",E69,D70)

The LOOKUP code that showed blank:

=IFERROR(LOOKUP(2,1/(E28:E82<>0),E28:E82),"")

The new code in 10 of the cells in the LOOKUP range:

=IF(AND(E80="",F79=""),"",IF(AND(E80="",F79<>""),F79,IF(AND(E80<>"",F79<>""),E80,IF(AND(E80<>"",F79=""),E80))))

The (same) LOOKUP code that now shows the last date entered in the range:

=IFERROR(LOOKUP(2,1/((F28:F82<>"")),F28:F82),"")

 

[Fluff]

Glad you sorted it & thanks for the feedback.