Using CONTAINS to check for two strings in a cell of a referenced sheet

Karisma

To put my question in perspective, I have a main asset sheet that lists the name of all the assets in the "New Equipment ID" column.

I have another sheet, let's call this the job sheet, that has multiple columns, including the "Status" (single select dropdown) and "System In Use" (multi-select dropdown) column that I have the contents concatenated into a cell in the hidden "asset state helper" (text) column.

i.e.

Status column = IN PROGRESS

System In Use column = ABCD-01

asset state helper column = "IN PROGRESS" + " " + [System In Use]@row = IN PROGRESS ABCD-01

In the main asset sheet, I have an "Asset State" column, in which I want to check if the "asset state helper" column in the job sheet includes "IN PROGRESS" and the New Equipment ID in the main asset sheet to decide whether or not the asset is in use - active or inactive.

The idea was this:

=IF(CONTAINS("IN PROGRESS" + " " + [New Equipment ID]@row, {job sheet asset state helper column}), "ACTIVE", "INACTIVE")

Although the formula works, the IF statement always results to "INACTIVE" even though the contents match. Using the HAS formula gives the same result. I also cannot use the HAS formula as the "System In Use" column may have more than one selection.

To troubleshoot this, if I was only searching for "IN PROGRESS" - it showed as active, or if I were only searching for the [New Equipment ID]@row it worked. I also brought a cell from "asset state helper" to the main asset sheet as a test and put

="IN PROGRESS" + " " + [New Equipment ID]@row

into another cell, checked to see if they were equal, and it resulted in "false" even though they were exactly the same...

My assumption is that it may have to do with the different types of columns being concatenated into a single text column, but shouldn't that text column be independent in a sense that the data that is contained in it is just a large text string in itself?

My end goal is much more complicated than this, but if I can figure out how to read whether the asset state helper column contains IN PROGRESS and the New Equipment ID, I can easily figure out the rest! You could call this a bottleneck in my overall plan, haha.

I'm usually pretty well-versed in creating formulas, but this seems to have a simple solution that I'm clearly stumped with.

Thanks in advance for the assistance, and apologies for the lengthy question.

Genevieve P.

Hi @Karisma

I believe this has to do with how your asset state helper column formula.

You say that this column as the formula:

= "IN PROGRESS" + " " + [System In Use]@row

And that the [System in Use] column is a Multi-Select column. This means that the returned value would actually have a CHAR(10) break between the "IN PROGRESS" text and the multi-select value, even if it's just one value selected.

Also, if there are more than one values selected, the returned result in the cell wouldn't have "IN PROGRESS" for each individual System... ex:

IN PROGRESS ABCD-01
IN PROGRESS ABCD-02

Instead the cell would show something like this:

IN PROGRESS
ABCD-01
ABCD-02

Is that correct? (Note: try adding wrap-text to this help column to see if the values appear on a different row).

In this instance, the formula won't find a match for your exact string put together because the IN PROGRESS isn't next to each item.

Instead, you'll want to search for two separate CONTAINS... if the cell contains both the "IN PROGRESS" text and if it contains the System in Use value, as separate values in the same cell.

Try this:

=IF(COUNTIFS({job sheet asset state helper column}, AND(CONTAINS("IN PROGRESS", @cell), CONTAINS([New Equipment ID]@row, @cell))) > 0, "True", "False")

Let me know if this makes sense!

Cheers,

Genevieve

Genevieve P.

Hi @Karisma

I'm glad this works for you!

The COUNTIFS is a personal preference, actually. You can write it as an IF(AND formula as well:

=IF(AND(CONTAINS("IN PROGRESS", {Multi}), CONTAINS([New Equipment ID]@row, {Multi})), "True", "False")

There's usually a few ways to reach the same end result with formulas. I prefer COUNTIFS with cross-sheet criteria because the structure makes sense in my mind... Count to see if there are any rows that Contain both these criteria. If there are, then the Count will return > 0... but if there's no match then it will be 0.

Hope that helps! 🙂

Genevieve

Genevieve P.

Hi @Karisma

I believe this has to do with how your asset state helper column formula.

You say that this column as the formula:

= "IN PROGRESS" + " " + [System In Use]@row

And that the [System in Use] column is a Multi-Select column. This means that the returned value would actually have a CHAR(10) break between the "IN PROGRESS" text and the multi-select value, even if it's just one value selected.

Also, if there are more than one values selected, the returned result in the cell wouldn't have "IN PROGRESS" for each individual System... ex:

IN PROGRESS ABCD-01
IN PROGRESS ABCD-02

Instead the cell would show something like this:

IN PROGRESS
ABCD-01
ABCD-02

Is that correct? (Note: try adding wrap-text to this help column to see if the values appear on a different row).

In this instance, the formula won't find a match for your exact string put together because the IN PROGRESS isn't next to each item.

Instead, you'll want to search for two separate CONTAINS... if the cell contains both the "IN PROGRESS" text and if it contains the System in Use value, as separate values in the same cell.

Try this:

=IF(COUNTIFS({job sheet asset state helper column}, AND(CONTAINS("IN PROGRESS", @cell), CONTAINS([New Equipment ID]@row, @cell))) > 0, "True", "False")

Let me know if this makes sense!

Cheers,

Genevieve

Karisma

Hi @Genevieve P.

Your solution to my problem is actually VERY intuitive! I figured out how to allow it to read as a string (using a JOIN function in the helper column as opposed to the logical expression I had), but your solution solved the issue I had with reading multiple values, if selected!

My only question for you is this - could you explain the reasoning behind using COUNTIFS in this formula? I understand that it gives the range of the column, but I'd also like to understand further how COUNTIFS(...) > 0, plays so well in this!

Also, thank you! This was super helpful, and I plan on using something similar in future sheets, so you were a big help.

Best,

Karisma

Genevieve P.

Hi @Karisma

I'm glad this works for you!

The COUNTIFS is a personal preference, actually. You can write it as an IF(AND formula as well:

=IF(AND(CONTAINS("IN PROGRESS", {Multi}), CONTAINS([New Equipment ID]@row, {Multi})), "True", "False")

There's usually a few ways to reach the same end result with formulas. I prefer COUNTIFS with cross-sheet criteria because the structure makes sense in my mind... Count to see if there are any rows that Contain both these criteria. If there are, then the Count will return > 0... but if there's no match then it will be 0.

Hope that helps! 🙂

Genevieve