Thread: find and match dates to return value

Hi,

On the 'project schedule' worksheet in the attached example, there are columns with date headings that can be populated by either an 'F', 'T', or '1' which correspond to a row for an employee. I need to display the next upcoming date populated with an 'F' (flight day) in the 'project schedule' in the 'next flight' cell of both the 'employees' and 'employee profile' worksheets for the matching employee. Is there a formula that would search and return the next upcoming 'F' on the worksheets?

Try this in D2, then copy it down:

=INDEX('Employee Profile'!$D:$D, MATCH($A2, 'Employee Profile'!$B:$B, 0) + 14)

or...

=IF($A2="", "",INDEX('Employee Profile'!$D:$D, MATCH($A2, 'Employee Profile'!$B:$B, 0) + 14))
(this can be copied down much further without errors)

Base on your request to lookup Project Schedule sheet..

and assuming you enter name in column D of that sheet...

try, in Employees!D2:

Code:

=INDEX('Project Schedule'!$E$13:$BE$13,MATCH("F",INDEX('Project Schedule'!$E$15:$E$42,MATCH($A2,'Project Schedule'!$D$15:$D$42,0)):INDEX('Project Schedule'!$BE$15:$BE$42,MATCH($A2,'Project Schedule'!$D$15:$D$42,0)),0))

and in Employee Profile!D16:

Code:

=INDEX('Project Schedule'!$E$13:$BE$13,MATCH("F",INDEX('Project Schedule'!$E$15:$E$42,MATCH(B2,'Project Schedule'!$D$15:$D$42,0)):INDEX('Project Schedule'!$BE$15:$BE$42,MATCH(B2,'Project Schedule'!$D$15:$D$42,0)),0))

then copy to each Next Flight cell down the list...

Note: If you don't unmerge cells in column D, you will have to paste the formulas in the formula bar...and may have to adjust reference to name in column B....

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)