Cactus1993
asked on
Excel Offset Formula Question
In the attached spreadsheet, I have two columns of data, with the top row returning the last cell of data with an offset formula, as it should. However, I'd like to get a formula that returns the last value in the column -- only if the one next to it is filled.
In my example, I'd like the date 01/31 returned, instead of the date 02/04.
How do I write this type of formula?
Thanks!
EE2.xlsx
In my example, I'd like the date 01/31 returned, instead of the date 02/04.
How do I write this type of formula?
Thanks!
EE2.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, guys. I like the OFFSET rather than the LOOKUP in this case, because only one formula needs to be entered. Works perfectly ... thanks!
I'm not suggesting you use any more formulas than you are currently using - the first LOOKUP replaces your B1 formula and the second replaces the A1 formula.
LOOKUP and MATCH use the same technique here to find the last value, but with MATCH you only get the position....and OFFSET is being used to get the value from that position.......but with LOOKUP you don't need that two stage approach - it just gives you the last value directly......
regards, barry
LOOKUP and MATCH use the same technique here to find the last value, but with MATCH you only get the position....and OFFSET is being used to get the value from that position.......but with LOOKUP you don't need that two stage approach - it just gives you the last value directly......
regards, barry
=LOOKUP(9.99E+307,B3:B366)
then in A1 to get the corresponding date try
=LOOKUP(9.99E+307,B3:B366,
regards, barry