Formula in Smartsheet

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

Find more posts tagged with

Accepted answers

KPH

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.

All comments

KPH

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.

HZAR

Hi KPH

Thanks for the point…now i know where is my mistake. Thanks 😍

KPH

Glad I could help @HZAR

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

KPH

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.

HZAR

Hi KPH

Sorry for not providing you enough details. I have attached the scenario from the vlook file that I have

Comm=Passive

Screenshot 2024-05-13 224350.png

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

=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

HZAR

Hi KPH

Sorry to miss out the screen shot for the Vlookup file

Screenshot 2024-05-13 224350.png

Passive comm

If CM Attention is Yes, Status-Need info, MSI CM review=CC

If Cm attention =No, All status is under Mike

KPH

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.

HZAR

Hi KPH

Example Q.xlsx

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

HZAR

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.

KPH

Great news @HZAR

I'm glad you have it working.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.