IF cell contains value from a list then...

[millss]

Hi,

I have the following formula which adds a year onto a date in cell B2. However, I now need to alter the formula to account for certain departments having an extension of 18 months.

I need the formula to say ‘If cell B2 contains a value from Column H then add 18 months, else add 12 months. My research had pointed me to use ISNUMBER(SEARCH) to see if a cell contains a value but it’s not working and having used Evaluate formula to step through it – I’m not convinced I’m using the right function.

Would really appreciate a steer in the right direction.

Formula I’ve tried:

=IF(ISNUMBER(SEARCH(A2,H:H)),DATE(YEAR(B2),MONTH(B2)+18,DAY(B2)),DATE(YEAR(B2)+1,MONTH(B2),DAY(B2)))

Thanks,
Extension.PNG

[63falcondude]

If cell B2 contains a value from Column H

Assuming that you meant A2 (not B2), try this in C2:

=EDATE(B2,IF(COUNTIF(H:H,A2),18,12))

Also, update your profile to show the current version of Excel that you are using. That screenshot is not of Excel 2003.

[Arkadi]

How about:

Please Login or Register  to view this content.

I see 63falcondude was faster and smarter