"IF" formula with multiple options

Question

I need to create a formula where if x is between certain dates, then a particular message will appear. But there are several options.

For example, a date is entered in cell A2

In another cell, I want a message to auto-populate based on the date entered in A2

So if A2 <2020-09-30, the message in C2 will say "The amount is v" AND

If A2>= 2020-09-30 and <2021-03-31, the message will say "The amount is w" AND

If A2>= 2021-03-31 and <2022-03-31, the message will say "The amount is x" AND

If A2>= 2022-03-31 and <2023-03-31, the message will say "The amount is y"

If A2>=2023-03-31, the message will say "The amount is z"

At the same time, I need another cell (say, B2) to autofill with the amounts w, x, y, or z based on the entry in A2.

For example:

DATE	Amount	MESSAGE
2022-02-21	x	The amount is x
2019-07-24	v	The amount is v
2023-05-10	z	The amount is z

How can I write a formula to include all the above options for the Message (column C), and another formula so the amount (column B) will auto-populate based on the same options?

I've tried IF, but I get an error saying I have too many arguments.

I've tried IF(AND) but I must be doing something wrong, because it doesn't work, either.

One of my attempts was something like this:

=IF((AND(B1<=DATE(2020,9.1),”Levy prior to Sept 2020 is $200”), IF(AND(B1>DATE(2020.9.1),B1<DATE(2021,3,31)), “Levy beginning Sept 1 2020 up to and including March 31 2021 is $204.40”), IF(AND(B1>=DATE(2021,3,31), B1<DATE(2022,3,31)), “Levy beginning March 31 2021 up to and including March 30 2022 is $208.07”), IF(AND(B1>=DATE(2023,3,31),”Levy beginning March 31 2022 up to and including March 30 2023 is $215.14”))

I probably have too many { } or maybe not enough.

Answer 1

Hi there

Dealing with long nested IF formulas are tricky.

In this scenario is advisable to use VLOOKUP() formulas for approximate matches using TRUE as the last argument.

To that purpose please, try the following steps

1. Anywhere on your workbook create a table similar to the one below
2. And give it a Named Range per example: "FYLevies"

To get the expected results

3. Use the following formulas

For the AMOUNT =VLOOKUP($A2,FYLevies,2,TRUE)

For the MESSAGE =VLOOKUP($A2,FYLevies,3,TRUE)

Table Structure Notes:

a) Dates most be sorted in ascending order

b) They represent the lower bound of the period (i.e the starting date for the period= included)

c) The date 01/01/1900 is the First Date in Excel, It represents all dates prior to 01-Sept-2020 in this table

d) I choose to change the message and to use formulas to make it dynamic.

So we could easily change the amount and dates in the table and the message will change accordingly.

e) The levy amount for 2023 onwards is fake, since data for that period was missing from the given details in the post.

You may download the file with this solution from the link below https://we.tl/t-TvpB7fjnkG

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

In B2:

=IF(A2<DATE(2020,9,30,v,IF(A2<DATE(2021,3,31),w,IF(A2<DATE(2022,3,31),x,IF(A2<DATE(2023,3,31),y,z))))

In C2:

="The amount is "&B2

Select B2 and C2, then fill down.