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.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

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.