Use If formula to subtract different numbers according to a specific word

[dquack]

I am trying to build a tracker for award timelines but we have different awards with different time requirements for submission. For a service award it needs to be to HR 15 days prior to today but if it's an achievement award it needs to be turned in to HR 30 days prior to today. I was looking to see if there was a way to use the If function to subtract different numbers depending on the word found in column B.

So column D would say if column B is "service" subtract 15 from C3 or if column B is "achievement" subtract 30 from C3. This would allow me to type in the award name then have excel correctly count the right number of days. I know some things on excel but anything beyond basic formulas are hard for me to build. Thanks in advance.

[#2 · Accepted solution]

@dquack 

Create a lookup table, for example on another sheet:

 

You can then use VLOOKUP:

The formula in D3 is

=C3-VLOOKUP(B3,Sheet2!$A$2:$B$3,2,FALSE)

 

[#3]

@Hans Vogelaar 

Thanks for the help and advice. It has been awhile since I used VLOOKUP and forgot the possibilities with that function. Your help was able to get the excel to work as desired with no issues. Thanks for the quick reply!