Returning a Date Value

[Saw59]

Hello all, I'm new to the forum.
I need help creating a formula to return a (date) found in row one if a the letter (C) is found in the corresponding row three.
So this is what I have...
I have a worksheet that I track Oil Analysis samples...the first row is the date that I take the sample on, if the oil needs changed I mark the cell below the date with a "C" indicating Change.
Row 1 is the dates on which the oil sample was collected.
Row 3 is the machine that the sample relates to. Underneath each date is a letter "S" for sample or the letter "C" for change, there are also empty cell in the row.
Cell E3 is the cell that indicates the (Last Oil Change)
I need a formula the will look at row 3...ignore the cells that are empty or contain the letter "S" find the last column to the right with the letter "C" in it, and return the corresponding date above in Row 1 to the cell E3.

Thanks in advance for any help

[CARACALLA]

Can you provide some sample data and what the expected outcome is?

[Saw59]

Attachment 629291I need a formula that will return the date in the top row above the farthest right Letter "C" to the cell next to the machine (Cell C3)
For example; P-01 last oil change date would be 7/1/2013, P-02 last oil change would be 12/25/2016, P-03 last oil change would be 7/1/2012, P-04 last oil change would be 7/1/2012

[CARACALLA]

Invalid Attachment specified.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[jason.b75]

Based on your description, a formula such as

Please Login or Register  to view this content.

should do what you ask.

[Saw59]

Thanks CARACALLA and jason.b75 for helping me.
Hopefully the File uploaded
Column E (Last Oil Change) needs to display the date in line 1 if there is a "C" in the column below the date.
The =MAXIFS(3:3,"C",1:1) formula gives me a Problem box with the formula.
Currently...as you can see... i have to enter the (last oil change) date by hand.

[jason.b75]

That was my error, the last bit of the formula should have been first, it should have been

Please Login or Register  to view this content.

that will return a circular reference, which you can work around by enabling iterative calculation, (in excel follow the ribbon / menu path, File > Options > Formulas and check the box to enable iterative calculations).

Alternatively, you could restrict the formula to a smaller number of columns, for example

Please Login or Register  to view this content.

In addition, you can make it easier to fill down by error trapping rows with no "C" with

Please Login or Register  to view this content.

enter the formula into E3, then select E3:E83, press F2, then Ctrl Enter to fill the formula to the column.