Explanation of this problem: Column N has dates in the form of Year, Month, because I am comparing that to an Initial Inspection Date in Column C1 through various number of C columns . N1 is the date after a month has past since the Initial Inspection Date and N2 is the after 6 month has past the Initial Inspection date. There is also Column O1 which say “New” and that is being compared to Column D1 through a various number of D Columns. In Column K, titled Notes, after the comparison is done, a status noted base on the Column N, Column C and Column D. My problem is that after 6 month, the Labwork status in column D if not change to close is “Past Due” and I need a way using this equation to note that in Column K. Below is the rest of the details for this problem. Since I can only use 3 argument for an IF statement, how can I add on to the equation to get it to provide the “Past Due Move to Close” status in Column K?
=IF((ISNUMBER(SEARCH($N$1,$C2))),(IF(ISNUMBER(SEARCH($O$1,$D2)),”Move to In-Process”,$D2)),(IF((ISNUMBER(SEARCH($N$2,$C2))),”Move to Close”,$D2)))
This equations above works. However, this equation below does not work.
=IF((ISNUMBER(SEARCH($N$1,$C2))),(IF(ISNUMBER(SEARCH($O$1,$D2)),”Move to In-Process”,$D2)),(IF(ISNUMBER(SEARCH($N$2,$C2)),”Move to Close”,$D2)),(IF((ISNUMBER(SEARCH($N$3,$C2))),”Past Due Move to Close”,$D2)))
According to Excel Spreadsheet, I have too many arguments for this function. The first equation uses 3 If, ISNUMBER, SEARCH for the equation. Can I use 4 of the IF, ISNUMBER, SEARCH to achieve my goal here?
Benjamin J. Temple is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
You can’t use the IF function in such way:
=IF(
(ISNUMBER(SEARCH($N$1,$C2))),
(IF(ISNUMBER(SEARCH($O$1,$D2)),"Move to In-Process",$D2)),
(IF(ISNUMBER(SEARCH($N$2,$C2)),"Move to Close",$D2)),
(IF((ISNUMBER(SEARCH($N$3,$C2))),"Past Due Move to Close",$D2))
)
It can be used in such way only:
=IF(condition;value_if_true;value_if_false)
Your task can be resolved in two ways.
- You can tie up IFs:
=IF(
condition1,
value_if_true1,
IF(
condition2,
value_if_true2,
value_if_false
)
)
- You can use the IFS function:
=IFS(
Something_is_True1,Value_if_True1,
Something_is_True2,Value_if_True2,
Something_is_True3,Value_if_True3,
and so on
)
It seems to me your formula can be like this:
=IF(
ISNUMBER(SEARCH($N$1,$C2)),
IFS(
ISNUMBER(SEARCH($O$1,$D2)),"Move to In-Process",
ISNUMBER(SEARCH($N$2,$C2)),"Move to Close"
ISNUMBER(SEARCH($N$3,$C2)),"Past Due Move to Close"
),
$D2)
)