Formula in Smartsheet
Hi
I have the formula as below and it working but when i add 1 more status it turn out error
=IF(AND([CM attention required]@row = "Yes", [PPV Status]@row = "MSI CM Review"), VLOOKUP(Commodity@row, {Category Managers Range 1}, 3, 0), VLOOKUP(Commodity@row, {Category Managers Range 1}, 2, 0))
Formula below have error since I have added 1 more status as need info
=IF(AND([CM attention required]@row = "Yes", [PPV Status]@row = "MSI CM Review","Need Info"), VLOOKUP(Commodity@row, {Category Managers Range 1}, 3, 0), VLOOKUP(Commodity@row, {Category Managers Range 1}, 2, 0))
May I know what is the correct formula? Pls help to advise. Thanks
Best Answer
-
Hi @HZAR
You need to include the range for the new criteria. See how the others are all pairs:
[CM attention required]@row = “Yes”
[PPV status]@row=“MSI CM Review”
but then you have “Need Info” without specifying which cell to look in. You need [column name]@row= before the criteria.
Answers
-
Hi @HZAR
You need to include the range for the new criteria. See how the others are all pairs:
[CM attention required]@row = “Yes”
[PPV status]@row=“MSI CM Review”
but then you have “Need Info” without specifying which cell to look in. You need [column name]@row= before the criteria.
-
Hi KPH
Thanks for the point…now i know where is my mistake. Thanks 😍
-
Glad I could help @HZAR
-
Hi KPH
I taught i understand what you mean but when I put in the one as below formula, it give diff name
=IF(AND([CM attention required]@row = "Yes", [PPV Status]@row = "MSI CM Review", VLOOKUP(Commodity@row, {Category Managers Range 1}, 3, 0), VLOOKUP(Commodity@row, {Category Managers Range 1}, 2, 0))-Answer:Jake
=IF(AND([CM attention required]@row = "Yes", [PPV Status]@row = "MSI CM Review", [PPV Status]@row = "Need Info"), VLOOKUP(Commodity@row, {Category Managers Range 1}, 3, 0), VLOOKUP(Commodity@row, {Category Managers Range 1}, 2, 0))-Answer: Matt….
By right i want if the status is under CM review or Need info, when CM attention required then it will remain to Jake
Need your help to advise. Thanks
-
Hi @HZAR
It looks like when you added the extra criteria you moved a parenthesis. Or maybe your VLOOKUP is not correct. I am not sure what you are trying to do, and cannot see your data, so will explain what the formula does which might help.
This one you say returns Matt
=IF(AND([CM attention required]@row = "Yes", [PPV Status]@row = "MSI CM Review", [PPV Status]@row = "Need Info"), VLOOKUP(Commodity@row, {Category Managers Range 1}, 3, 0), VLOOKUP(Commodity@row, {Category Managers Range 1}, 2, 0))
The part in bold means if in the current row CM attention required is "Yes", PPV Status is "MSI CM Review" and PPV Status is "Need Info" then do the next thing. The VLOOKUP then looks at another table and returns the value from column 3 if the first column in the table contains the same value as Commodity in the current row. If the part in bold is not true then the value from column 2 is returned instead. The 0s at the end of each VLOOKUP might be doing something unexpected as the valid data there is True or False. Adding a 0 won't break anything but I am not sure what it will do. You can use false to return the first exact match.
VLOOKUP(Commodity@row, {Category Managers Range 1}, 3, false)
This one, you say, returns Jake, which is what you want, but to be honest I have no idea how it is even working:
=IF(AND([CM attention required]@row = "Yes", [PPV Status]@row = "MSI CM Review", VLOOKUP(Commodity@row, {Category Managers Range 1}, 3, 0), VLOOKUP(Commodity@row, {Category Managers Range 1}, 2, 0))
In this one there is no parenthesis to close the AND function so the VLOOKUP is also a criteria, rather than an answer, but there is no = and no closing parenthesis. I would expect this to be unparseable.
-
Hi KPH
Sorry for not providing you enough details. I have attached the scenario from the vlook file that I have
Comm=Passive
If MR Manager attention required=Yes, Status=MSI MR Review & Need info, then the name should be under CC
If MR Manager attention=No, all the status will be under Mike
How do I rearrange the formula that I have since I need to add the status "Need info" to the below formula which is currently working if under MSI CM review, MR Manager required =Y. . Pls help to advise. Thanks
Previous
=IF(AND([CM attention required]@row = "Yes", [PPV Status]@row = "MSI CM Review"), VLOOKUP(Commodity@row, {Category Managers Range 1}, 3, 0), VLOOKUP(Commodity@row, {Category Managers Range 1}, 2, 0))
As per suggestion
=IF(AND([CM attention required]@row = "Yes", [PPV Status]@row = "MSI CM Review", [PPV Status]@row="Need Info"),VLOOKUP(Commodity@row, {Category Managers Range 1}, 3, false), VLOOKUP(Commodity@row, {Category Managers Range 1}, 2, false))-give me Mike instead of CC
-
Hi KPH
Sorry to miss out the screen shot for the Vlookup file
Passive comm
If CM Attention is Yes, Status-Need info, MSI CM review=CC
If Cm attention =No, All status is under Mike
=IF(AND([CM attention required]@row = "Yes", [PPV Status]@row = "MSI CM Review", [PPV Status]@row = "Need Info"), VLOOKUP(Commodity@row, {Category Managers Range 1}, 3, 0), VLOOKUP(Commodity@row, {Category Managers Range 1}, 2, 0))-it me mike instead f CC
-
I'm not sure I understand how it is returning both the wrong row and the wrong column but think the column issue could be the use of AND rather than OR
At the moment…
If CM attention required=Yes, Status=MSI CM Review AND Need info, then it returns the 3rd column.
If ANY of those things are false it will return the 2nd column.
I don't think you really do want to use AND. It sounds like you might mean OR. In other words, if any one of the things are true it should return the 3rd column. If so change the AND to OR.
If this is not the issue, please share a screenshot of the data you have (with the attention required, stats and commodity rows) and the outcome you want to see.
-
Hi KPH
I have attached the scenario file for your end to advise how that I can create the desire output. Hope my example is good for your end. Appreciate your help to review and advise
-
Hi @KPH
I already have the way to get the output that I need. Need to change the AND to OR plus add one more if function in the formula before the vlookup. Thanks for the help all the way and explanation that you have put in details. You awesome.
-
Great news @HZAR
I'm glad you have it working.
Help Article Resources
Categories
Check out the Formula Handbook template!
