In Google Sheets, I want to check row-wise if any of the numeric or text values in a list are present in columns. In short, I want to match a list row-wise in columns and return TRUE or FALSE.

Of course, we can get the required output by combining IF, AND, OR Logical Functions in Google Sheets.

But, If the list is long, or there are several columns in your dataset/table, using the above combination may be tiresome.

Here is why and what’s the solution to it.

Example – Check Row-Wise If Any of the Values Are Present

In the following example, I want to check row-wise if any of the values in H2:H4 are present in columns in the range A2:D8.

With two of the functions in the said combinations, i.e., IF and OR, we can write the formula as below for cell F2 and then copy-paste it down.

=if(
     or(
        or(A2="Clara",A2="Ann",A2="Steven"),
        or(B2="Clara",B2="Ann",B2="Steven"),
        or(C2="Clara",C2="Ann",C2="Steven"),
        or(D2="Clara",D2="Ann",D2="Steven")
     ),
     TRUE,FALSE
)

It’s not an ideal solution to match a list row-wise in columns in Google Sheets as we can’t easily edit the formula when we include more columns in the data range (A2:D8) or more values in the list (H2:H4).

I have better solutions to check row-wise if any of the values are present in columns in Google Sheets.

Match a List Row-Wise in Columns in Google Sheets

I have two types of Google Sheets formulas here – Array and Non-Array (Self-expanding).

1. The Non-Array Formula to Check Row-Wise If Any of the Values in a List Are Present

Since the above formula is not flexible enough, we may consider using an alternative one, and I prefer a REGEXMATCH here.

The advantage of this new formula is we can easily edit it when we include more values in the list or more columns in the dataset (also called table or database).

=countif(ArrayFormula(regexmatch(A2:D2&"","^"&textjoin("$|^",true,$H$2:$H$4)&"$")),true)>0

We can use the above formula in cell F2.

Since it’s a non-array formula, we should copy-paste it down.

How does this formula able to match a list row-wise in columns?

Formula Explanation

Syntax:-

COUNTIF(ArrayFormula(REGEXMATCH(text, regular_expression),TRUE))>0

regular_expression – "^Clara$|^Ann$|^Steven$"

The list in H2:H4 contains three names, and they are “Clara,” “Ann,” and “Steven.”

In the above formula, the pipe symbol ( | ) serves as the OR.

The caret ( ^ ) and the dollar signs ( $ ) match the beginning and end of a line, respectively. Both of these prevent partial matches.

text – A2:D2&"" (&"" adds a null character to covert numeric values, if any, to text)

I want to check the row range A2:D2 if any of the values (here names) are present.

The below formula will do that.

=ArrayFormula(regexmatch(A2:D2&"","^Clara$|^Ann$|^Steven$"))

Result:-

We can replace the regular_expression "^Clara$|^Ann$|^Steven$" with "^"&textjoin("$|^",true,$H$2:$H$4)&"$".

=ArrayFormula(regexmatch(A2:D2&"","^"&textjoin("$|^",true,$H$2:$H$4)&"$"))

What we should do next is to count the TRUE values returned.

If the count returns greater than 0, the output should be TRUE, else FALSE.

The COUNTIF takes care of that.

This way, we can check row-wise if any of the numeric or text values are present in columns in Google Sheets.

2. The Array Formula to Check Row-Wise If Any of the Values in a List Are Present

Here I am using numeric values but work equally well with the above text values.

It’s impossible to convert the above formula to match a list row-wise in columns in Google Sheets.

It’s not because of the REGEXMATCH. Then?

We can use REGEXMATCH for all the rows in the table by replacing the text argument A2:D2 with A2:D8 as below.

=ArrayFormula(regexmatch(A2:D8&"","^"&textjoin("$|^",true,$H$2:$H$4)&"$"))

There are no changes in the regular_expression.

But then our COUNTIF won’t serve the purpose.

I have explained earlier how to Countif Across Columns Row by Row in Google Sheets.

There I have used DCOUNT to replace COUNTIF.

Here also we can follow that approach to check row-wise if any of the values are present.

Please follow the below steps.

Steps

Syntax:-

DCOUNT(database, field, criteria)

Here the database is the above (last) REGEXMATCH formula.

We should convert the TRUE and FALSE values returned by the REGEXMATCH (database) to 1 and null.

So the database is (only the bold part is required);

ArrayFormula(IF(regexmatch(A2:D8&"","^"&textjoin("$|^",true,$H$2:$H$4)&"$")=TRUE,1,))

In our above-mentioned tutorial (please open and check that), the database is if(A2:E>=10,A2:E,).

Here I am just replacing that with our ‘database’ (please refer to the bold part in the formula below), and the formula is as follows.

=ArrayFormula(dcount(transpose({row(A2:A8),IF(regexmatch(A2:D8&"","^"&textjoin("$|^",true,$H$2:$H$4)&"$")=TRUE,1,)}),sequence(rows(A2:A8)),{if(,,);if(,,)}))

Note:- You should empty F2:F before inserting this formula in F2.

The above formula is enough to match a list row-wise in columns in Google Sheets.

But it would return 1 for the match and 0 for the mismatch.

We can solve that, I mean convert to TRUE/FALSE, by modifying the DCOUNT as below.

=ArrayFormula(if(dcount(transpose({row(A2:A8),IF(regexmatch(A2:D8&"","^"&textjoin("$|^",true,$H$2:$H$4)&"$")=TRUE,1,)}),sequence(rows(A2:A8)),{if(,,);if(,,)})>=1,true,false))

You can follow the above array/non-array formulas to check row-wise if any of the values are present in columns in Google Sheets.

Thanks for the stay. Enjoy!