Lookup Formula Help

**mpjennings25** · 07-01-2013, 10:40 AM

Date Example.xlsx

This is a simplified example of a much larger workbook that I am working on. I need formulas to insert into the yellow highlighted cells to find the start date and end date of each "fund" (each column). Each fund starts with a $0 balance, and increases by $1 every period until it hits a maximum limit (in this case, 5). After each 6 month period, part of the fund rolls over into the next "open" fund that has not yet hit its max limit of 5. I need the start date row to have a function that looks up the date of the first cell in the column that is greater than zero. I also need a formula for the end date to lookup the date in which each fund first hits the limit of 5.

I tried using some different INDEX and MATCH formulas, but none of them worked properly. Help would be greatly appreciated!

**Pete_UK** · 07-01-2013, 11:18 AM

Put this array* formula in C3:

Formula:

Please Login or Register  to view this content.

and this array* formula in C4:

Formula:

Please Login or Register  to view this content.

Ensure that they are formatted as dates, then copy across as far as required.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>.

Hope this helps.

Pete

**zbor** · 07-01-2013, 11:21 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**mpjennings25** · 07-01-2013, 12:45 PM

Originally Posted by Pete_UK

Put this array* formula in C3:

Formula:

Please Login or Register  to view this content.

and this array* formula in C4:

Formula:

Please Login or Register  to view this content.

Ensure that they are formatted as dates, then copy across as far as required.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>.

Hope this helps.

Pete

Thank you very much - you're a life saver!