Check Row-Wise If Any of the Values Are Present in Columns

In Google Sheets, there’s a simple yet powerful technique to check row-wise if any of the values from a list are present in columns. This technique allows you to match a list row-wise in columns and get a TRUE or FALSE outcome. Let me show you how.

The Traditional Approach

By combining various logical functions like IF, AND, and OR, you can achieve the desired result. However, this method can become cumbersome and time-consuming, especially when dealing with large datasets or long lists.

A Better Solution

To make the process easier and more efficient, I have two types of formulas for you – Array and Non-Array (Self-expanding).

1. The Non-Array Formula

Let’s start with the Non-Array formula, which relies on the REGEXMATCH function. This formula offers flexibility, allowing you to easily edit it when adding more values to the list or additional columns to the dataset.

=COUNTIF(ArrayFormula(REGEXMATCH(A2:D2&"","^"&TEXTJOIN("$|^",TRUE,$H$2:$H$4)&"$")),TRUE)>0

In this formula, we are checking if any of the values in the range A2:D2 match the names in the list H2:H4. The REGEXMATCH function, along with the TEXTJOIN function, performs the necessary comparisons. If there is a match, the formula returns TRUE; otherwise, it returns FALSE.

2. The Array Formula

Now, let’s move on to the Array formula. This formula works equally well with numeric values as it does with text values.

=ArrayFormula(DCOUNT(transpose({row(A2:A8),IF(REGEXMATCH(A2:D8&"","^"&TEXTJOIN("$|^",TRUE,$H$2:$H$4)&"$"),1)}),SEQUENCE(ROWS(A2:A8)),{IF(,,);IF(,,)}))>=1

To implement this formula, we use REGEXMATCH to compare each row in the range A2:D8 with the list of names in H2:H4. The resulting matches are counted using the DCOUNT function. If the count is greater than or equal to 1, the formula returns TRUE; otherwise, it returns FALSE.

These formulas allow you to effortlessly check row-wise if any of the values from a list are present in columns in Google Sheets.

You can use the formula that best suits your needs and enjoy the simplicity and efficiency it brings.

Thanks for reading! If you want to discover more Google Sheets tips and tricks, head over to Crawlan.com. Stay tuned for more exciting content!