Nested If Formula Difficulty

[drsglsals]

Hello, I am trying to make a cell display a certain date using nested IF arguments or Vlookup if that is more appropriate?.

For example, When a date is manually entered into A1, B1 should then display another date that precedes the date in A1 by either 30, 60, 90, or 120 days prior to the date in A1. Cell C1 is the criteria for how many days B1 should precede A1. Cell C1 is always text.

Continuing the example, dog=30, cat=60, mouse=90, rat=120

If 15-Apr-16 is entered in A1, and C1 is "cat", then B1 should display 14-Jun-16.

Thank you

 

[gaz_chops]

Welcome to the forum.

Try

=A1-LOOKUP(C1,{"Cat","Dog","Mouse","Rat"},{60,30,90,120})

Realised you said precedes, but the example shows a date later! If you want to add 60 days change the minus to a plus.

 

[drsglsals]

That worked perfectly! Thank you. And my mistake on using a date further in time after asking about preceding.

 

[gaz_chops]

You're welcome.

 

[drsglsals]

Gaz_Chops, I have hit another sticking point if you are willing to assist further. Using my original example, I need to add two more potential answers in C1 "frog" and "toad". If frog or toad are selected, I need B1 to not compute anything and remain blank or display "N/A".

Thank you

 

[gaz_chops]

Try

=IF(OR(C1={"Frog","Toad"}),"",A1-LOOKUP(C1,{"Cat","Dog","Mouse","Rat"},{60,30,90,120}))

 

[drsglsals]

Thank you again!

 

[gaz_chops]

You're welcome.