If D22 equals "Weekly" then L22 Date+7

[Sumeluar]

Hello all! D22=E5, E5 data validated to Daily, weekly, Monthly, .... So if E5="Weekly" (no quotes) thenD22=E5 therefore L22=Date+7. Basicaly I want L22 to add 1, 7, 14, 30, 120 days depending on what is selected on E5.

Is probably a beginers question, but being new to this doesn't help.

Any sugestions, much appreciated!

Regards!

Sumeluar

 

[mikerickson]

In L22
=TODAY() + HLOOKUP(E5,{"daily","weekly","fortnightly","monthly","triannualy";1,7,14,30,120},2,FALSE)

 

[jim may]

In A1:B5

Daily 1
Weekly 7
Bi-Weekly 14
Monthly 30
Quarterly 120

use:
=TODAY()+VLOOKUP(F1,A1:B5,2,FALSE)

 

[Sumeluar]

You guys are awesome! Thank you, Thank you, Thank you for your quick and accurate response.

Regards!

Sumeluar

 

[jim may]

Using the HLookup - it doesn't seem to tolerate substituting for "daily", "weekly", etc
cell references such as $F$1, $F$2, etc where these contain "daily", (w/o the quotes).
Why is that?

 

[barry houdini]

Using the HLookup - it doesn't seem to tolerate substituting for "daily", "weekly", etc
cell references such as $F$1, $F$2, etc where these contain "daily", (w/o the quotes).
Why is that?

Hello Jim,

HLOOKUP should work in exactly the same way as the VLOOKUP you proposed except the range would need to be transposed, e.g. if A1:E1 contained "daily", "weekly" etc and A2:E2 the numbers, then formula would be

=TODAY()+HLOOKUP(F1,A1:E2,2,FALSE)

 

[jim may]

Thanks Barry;
My question has to do with Mikerickson's proposed formula, as follows:

=TODAY() + HLOOKUP(E5,{"daily","weekly","fortnightly","monthly","triannualy";1,7,14,30,120},2,FALSE)

I tried substituting $F$1 for "daily", $F$2 for "weekly", etc.. but it wouldn't work.
That was my question.
Tks,

Jim

 

[mikerickson]

From Excel Help "array constants"

"Array constants cannot contain cell references, columns or rows of unequal length, formulas, or the special characters $ (dollar sign), parentheses, or % (percent sign)."

(Yet the array formula ={"$";")";"%"} worked fine for me)