Conditional Excel formula to identify right-most column with a value?
February 25, 2014 7:25 PM Subscribe
I'm grappling with an Excel formula. Given a bunch of columns, I'd like to to (a) identify the right-most column that contains a value, then (b) fill a cell with that value and the value in the column heading.
posted by obiwanwasabi to Computers & Internet (10 answers total) 1 user marked this as a favorite
I have a spreadsheet with columns (each one is a project milestone). As the project progresses, a date is entered in the next column. For example:
A B C D E
1 Jan 1 Feb 1 Mar
1 Jan 1 Feb 1 Mar 1 Apr 1 May
The first project has completed milestones A, B, and C; the second has completed milestones A, B, C, D, and E.
I need a formula that looks across the row, identifies the most recent milestone, and then fills a cell with the name of the milestone (eg A, B, or C), and the date that milestone was reached. (If it matters, in real life the dates use a dd-mm-yyyy format, and the column heading is a text value, like 'Launch Approval'.)
For example, for the first project, the value would be "C;1 Mar" (the most recent milestone was C, and that happened on 1 March).
I've done this before (it's a UNIX system! I know this!), but I can't for the life of me remember how.
I'm pretty sure I worked from right to left, and used a bunch of nested IF and ISBLANK statements: if column E is blank, then check column D; if that's blank, check column C; and so on - once it found a non-blank cell, insert the heading for that column plus the date as a text value. For whatever reason, I can't get this working this time.
I don't need any fancy error checking (eg, 'well, there's a value in the column, but not the one to the left of it' or 'but the date in this column is earlier than the one to the left!'). Just the right-most date and the appropriate column heading.
I'd settle for each being in a separate cell (eg C in one cell, the date in the next) if that's any easier.
My Google Fu has proven weak. Save me, Excel wizards.