I could use some help with Excel's LOOKUP function please...
I have this formula designed to reference the last value (they are dates) in a row (non blank) and then return me the value in the title row above it. This one works great...
=INDEX($L$4:$BB16,1,((LOOKUP(2,1/(L16:BB16<>""),COLUMN(L16:BB16)))-10))
However I now need to also add the criteria that the date can not be bigger than today's date. So I've added an AND function in the criteria hoping that would do the trick as follows:
=INDEX($L$4:$BB10,1,((LOOKUP(2,1/(AND(ISNUMBER(L10:BB10),L10:BB10<=TODAY())),COLUMN(L10:BB10)))-10))
but this formula doesn't seem to be working. I'm not too sure why. In a nutshell I need the Lookup function to be able to handle two criteria (not blank and also date less than today). Any thoughts or advice??
Thanks!
