Excel 2007 : find and match dates to return value

[colins]

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?

[JBeaucaire]

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)

[NBVC]

Base on your request to lookup Project Schedule sheet..

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

try, in Employees!D2:

Please Login or Register  to view this content.

and in Employee Profile!D16:

Please Login or Register  to view this content.

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....

[colins]

The first answer was close, thanks JBeaucaire, but the solution developed by NBVC seems to be exactly what I need. NBVC you came through for me again, thanks ever so much!

[JBeaucaire]

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.)