Are IF, Thens possible in Excel?

[Phlegon_of_Tralles]

Okay, I've attached a worksheet. This worksheet has people who have received a merit over the past two years. Some have received more than one. Also on this sheet is other pay increases (ex. HIRE), but I am not concerned with these.

So, I want a formula that will scan the people and determine the most recent merit.

For example, the first person on the excel sheet is Tommy Gunn. Column C and D should be 'PAY' and 'MER' (if not, i don't want a date for them) and I want the most recent date in the column 'Most Recent Merit'.

Is this possible? I was trying to come up with an IF statement, but I realized I don't know how to get to the *newest* date. My formula is:
=IF(AND(A3=A4,C3="PAY",D3="MER"),B3,"")

[spitfireblue]

Hi,

Try this:

Please Login or Register  to view this content.

It is an array formula so you will need to confirm with Ctrl + Shift + Enter

[Phlegon_of_Tralles]

Thanks but that doesn't seem to work. I've uploaded an excel sheet with that formula, and as you can see Tommy Gunn doesn't have anything in column E. He should have 1/1/17.

[Phlegon_of_Tralles]

My knowledge of excel is intermediate - *at best* and I'm trying to figure this out. I'm not sure what the formula is trying to do with the 'true' part (=B4,B4).


=IF(LARGE(IF(($A$2:$A$8=A2)*($C$2:$C$8="PAY")*($D$2:$D$8="MER"),$B$2:$B$8),1)=B4,B4,"")

[humdingaling]

in E2 put

Please Login or Register  to view this content.

fill down

the first criteria needs to be in line with the end bits (note the bits in red)

updated your sheet with the formula

[spitfireblue]

Oops sorry... thanks for clarifying humdingaling.