How do I verify if text in one column has a match in the text of another column?

A.J.

I have two very lengthy columns of names on one sheet. How can I determine which names appear on both columns and which solely appear on the left (longer) column? I really need to know where there are matches and where there aren't. I know this might be simplistic, but I'm fairly new to Smartsheet. Thanks for any help.

Mike TV

@A.J.

It's actually working. You just have your formula backwards from what your expectations are.

The formula you're using does the following. It checks the box if the name in the Responded column that is in the same row as the check box is found anywhere in the Invite List column. So in Row 7 the name in the Responded column is Azeem Ahmad so Row 7 has the box checked because Azeem Ahmad name is in Row 6 of the Invite List column.

Maybe you need to switch up your formula to be the following if your expectation is that the box should be checked if the name to the left of the check box is found anywhere in the Responded column. Like so:

=IF(HAS(Responded:Responded, [Invite List]@row), true, false)

Mike TV

@A.J.

Here's one way to do it.

Name in both columns formula:

=IF(HAS([Name Column B]:[Name Column B], [Name Column A]@row), true, false)

For the way this formula is written, you'd make the "Name in both columns" column a checkbox column. What the formula does is to check the box if the name in Name Column A to the left of it's box (in it's row) exists anywhere in the Name Column B column.

Another way you could do it is like this:

Name Verify formula:

=IF(HAS([Name Column B]:[Name Column B], [Name Column A]@row), "The name " + [Name Column A]@row + " exists in both columns", "The name " + [Name Column A]@row + " exists only in Name Column A")

I'd go with the first way of doing it though.

Mike TV

@A.J.

One thing I could mention is the reason for using HAS in the formula. A good example is the names Sam and Sampson. If you use CONTAINS it would say Sam exists in both columns even though it doesn't. The HAS function requires an exact match. That's why you'd want to use HAS instead of CONTAINS.

A.J.

Hi Mike! Thanks so much for the help. I used the first method you described, but it's only checking the box when there is anything in both the column to the right and the column to the left. It doesn't seem to be doing an actual matching. This is the formula I'm using: =IF(HAS([Invite List]:[Invite List], Responded@row), true, false) When I add the brackets around "responded" the system removes them when I hit enter. No idea on that.

Mike TV

@A.J.

With that formula, it will be looking at the cell to the same row as the checkbox in the Responded column to confirm if that name is anywhere in the column called Invite List. What other action would you prefer it to take?

The reason SmartSheet is removing the brackets you put around Responded is because the column name is one word. The brackets won't be required for a column called Responded but it would be for a column called Respondent Name. Additionally, you can refer to an entire column called Responded with just Responded:Responded instead of [Responded]:[Responded]. It will just remove the brackets because without a space in the column name they're not required.

A.J.

Thanks for your help! Here's an image of my sheet. As you can see, Azeem Ahmad appears in both columns but the box isn't checked. This phenomenon is happening in multiple circumstances with that formula.

Mike TV

@A.J.

It's actually working. You just have your formula backwards from what your expectations are.

The formula you're using does the following. It checks the box if the name in the Responded column that is in the same row as the check box is found anywhere in the Invite List column. So in Row 7 the name in the Responded column is Azeem Ahmad so Row 7 has the box checked because Azeem Ahmad name is in Row 6 of the Invite List column.

Maybe you need to switch up your formula to be the following if your expectation is that the box should be checked if the name to the left of the check box is found anywhere in the Responded column. Like so:

=IF(HAS(Responded:Responded, [Invite List]@row), true, false)