Link to home
Start Free TrialLog in
Avatar of Cactus1993
Cactus1993Flag for United States of America

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
ASKER CERTIFIED SOLUTION
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You could use LOOKUP to get the last time from column B in B1, i.e.

=LOOKUP(9.99E+307,B3:B366)

then in A1 to get the corresponding date try

=LOOKUP(9.99E+307,B3:B366,A3:A366)

regards, barry
Avatar of Cactus1993

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