How to update a cell with "if / and" condition

Hi, I am trying to figure out the automation or formula to auto update a cell with "if / and" conditions are met.

Please help me achieve this.

If Column 'Headset' values is = Column 'Asset ID' value and Status Column of same row of Column 'Headset' is 'Shipped', update Availably Column, same row of Column 'Asset ID' to not available.

Find more posts tagged with

Accepted answers

Andrée Starå

Hi @Sugandha Joshi

Try something like this.

=IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped", "No", "Yes")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@Sugandha Joshi

Here's another one with an error check.

=IFERROR(IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped", "No", "Yes"), "Error")

All comments

Bassam Khalil

Hi @Sugandha Joshi

Hope you are fine, i will try to do it for you but could you please confirm that all column in one sheet or in deferent sheets.

Shrestha

@Bassam.M Khalil

Thank you for your swift action. Any workaround or formula would be a great help.

Best regards,

Andrée Starå

Hi @Sugandha Joshi

Try something like this.

=IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped", "No", "Yes")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Andrée Starå

@Sugandha Joshi

Here's another one with an error check.

=IFERROR(IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped", "No", "Yes"), "Error")

Bassam Khalil

Hi @Sugandha Joshi

Sorry for delay, please find in the following the soluation:

1- i create "Store Database"

2- I create "Shipping Database"

3- The Formula to check the Availability column ( Symbol Type ) status =IF(VLOOKUP([Asset ID]@row, {Shipping Database Status}, 3, 0) = "Shipped", "Red", "Green")

the following is a link to those database so you can check

Store Database

https://publish.smartsheet.com/53898c31e7f541b18a377f3775a7b2de

Shipping Database

https://publish.smartsheet.com/347a7e1b159040c1be41e06f52d79432

Shrestha

@Bassam.M Khalil

Thank you very much for your help and time. I really appreciate it. My data is all in one sheet.

@Andrée Starå

The formula worked perfectly fine and this is exactly what I was looking for.

Just one more question, how can I add one more condition within this formula?

e.g. "Shipped" OR "In Progress"

=IFERROR(IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped" OR "In Progress", "No", "Yes"), "Error") - this didn't work

Thank you in advance.

Andrée Starå

@Shrestha

I hope you're well and safe!

Excuse the late response.

Did you get it working?

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Shrestha

@Andrée Starå

I still need help on this.

how can I add one more condition within this formula?

e.g. "Shipped" OR "In Progress"

=IFERROR(IF(INDEX(Status:Status, MATCH([Asset ID]@row, Headset:Headset, 0)) = "Shipped" OR "In Progress", "No", "Yes"), "Error") - this didn't work

Thank you in advance.

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.