Match Two Columns with Values in Any Order Using REGEXMATCH

When you want to match values in two columns in any order, you can use various approaches in Google Sheets. One effective option is using REGEXMATCH. The advantage of REGEXMATCH is its flexibility, allowing you to perform case-sensitive or case-insensitive matches.

Example:

=ArrayFormula(
   IF(
      A2:A="",,
      REGEXMATCH(A2:A, "(?i)^"&TEXTJOIN("$|^", TRUE, UNIQUE(B2:B))&"$"))
)

This formula matches the values in columns A and B, returning TRUE wherever a match is found in the same row or any row in the range, and FALSE otherwise.

Note: This is a text function and works only with text values. If the columns contain numeric values, use XMATCH instead. Alternatively, you can format numeric values as text and apply REGEXMATCH, but this may lead to issues such as 10.2 not matching 10.20.

Matching Numeric Columns

For numeric values, use the following XMATCH formula:

=ArrayFormula(
   IF(
      A2:A="",,
      XMATCH(A2:A, B2:B)
   )
)

This matches numeric values in column A with those in column B, effectively matching values between two columns in any order.

Example: Matching Two Text Columns with Values in Any Order

Assume you have item names in columns A and B that are unordered. Use the following formula in cell C2:

=ArrayFormula(
   IF(
      A2:A="",,
      REGEXMATCH(A2:A, "(?i)^"&TEXTJOIN("$|^", TRUE, UNIQUE(B2:B))&"$"))
)

This formula returns TRUE for rows where the value in column A has a match in column B, and FALSE otherwise. Items with FALSE are not present in column B.

Example of Match Two Columns with Values in Any Order (Matching values from Column A with Column B)

To check items in column B that are not in column A, use:

=ArrayFormula(
   IF(
      B2:B="",,
      REGEXMATCH(B2:B, "(?i)^"&TEXTJOIN("$|^", TRUE, UNIQUE(A2:A))&"$")
   )
)
Example of Match Two Columns with Values in Any Order (Matching values from Column B with Column A)

To perform a case-sensitive match, remove (?i) from the formulas.

Example: Matching Two Numeric Columns with Values in Any Order

For numbers or dates in unordered columns, use the following XMATCH formula instead of REGEXMATCH, as REGEXMATCH is designed for text fields:

=ArrayFormula(
   IF(
      A2:A="",,
      IFNA(XMATCH(A2:A, B2:B))
   )
)

The formula returns a blank for items in column A that it does not find in column B. To check items in column B that are not in column A, use:

=ArrayFormula(
   IF(
      B2:B="",,
      IFNA(XMATCH(B2:B, A2:A))
   )
)

Note: These formulas work for text columns as well, but unlike REGEXMATCH, XMATCH is not case-sensitive.

Additional Tips: Filtering

You can use the above formulas as filter conditions. Here is the syntax for the FILTER function:

FILTER(range, condition1, [condition2, …])

Example: Filtering Column A for Items Not in Column B

=FILTER(A2:A, NOT(IF(A2:A="",,REGEXMATCH(A2:A, "(?i)^"&TEXTJOIN("$|^", TRUE, UNIQUE(B2:B))&"$"))))

This extracts values from column A that are not present in column B.

Similarly, to filter column B for values not in column A:

=FILTER(B2:B, NOT(IF(B2:B="",,REGEXMATCH(B2:B, "(?i)^"&TEXTJOIN("$|^", TRUE, UNIQUE(A2:A))&"$"))))

You can replace the condition with XMATCH as well.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Filter Top N per Category in Excel (Using FILTER & COUNTIFS)

You can use a combination of FILTER and COUNTIFS to filter the top N...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.