Matching multiple values in a column in Google Sheets is straightforward but involves one key consideration: case sensitivity.

We generally follow three types of matching:

1. Case-insensitive matching
2. Case-sensitive matching
3. Partial matching (which can also be case-sensitive or case-insensitive)

Let’s use a sample list of auto spare parts in the range A2:A in a Google Sheets file. Suppose you have an inquiry for three parts: Battery Box, Battery Plate, and Battery Cap.

Goal:

Find out if all three parts are available by checking the inventory (in column A) with a formula. We want to match these multiple values and count the matches. If the count of matches equals the number of required parts, it means all parts are available.

Below are examples of how to achieve this.

Match Multiple Values in a Column: Case-Insensitive Formula

We’ll list the search values (Battery Box, Battery Plate, Battery Cap) in C2:C, and the auto parts inventory is in A2:A.

We’ll use the LET function to name C2:C as the “criteria” and A2:A as the “range,” simplifying the formula.

Formula #1 (Case-Insensitive Match):

=ArrayFormula(
     LET(
          range, A2:A, criteria, C2:C,
          IF(
               COUNT(MATCH(criteria, range, 0))=COUNTA(criteria),
               "In Stock","No Stock"
          )
     )
)

If you want to include the part names directly in the formula instead of referring to C2:C, replace C2:C with this:

VSTACK("Battery Box", "Battery Plate", "Battery Cap")

Explanation of Formula #1:

The MATCH function looks for each value in the “criteria” (Battery Box, Battery Plate, Battery Cap) within the “range” (A2:A). It returns the relative position for matches and #N/A for mismatches.

The IF statement checks if the COUNT of matches equals the total number of search criteria. If they match, it returns “In Stock”; otherwise, it returns “No Stock.”

Can we use XMATCH instead of MATCH?

Yes! Just replace MATCH(criteria, range, 0) with XMATCH(criteria, range).

Match Multiple Values in a Column: Case-Sensitive Formula

To differentiate between uppercase and lowercase letters when matching multiple values, we use the following formula:

Formula #2 (Case-Sensitive Match):

=ArrayFormula(
     LET(
          range, UNIQUE(A2:A), criteria, UNIQUE(C2:C),
          IF(
               SUM(--REGEXMATCH(range, "^"&TEXTJOIN("$|^", true, criteria)&"$"))=COUNTA(criteria),
               "In Stock", "No Stock"
          )
     )
)

For example, “AQ101A” and “AQ101a” would be treated as different items in this formula, while Formula #1 would treat them as the same.

Explanation of Formula #2:

The REGEXMATCH function checks each item in the “range” (A2:A) against the criteria (C2:C) using regular expressions. It returns 1 for matches and 0 for non-matches. We then use SUM to count these matches.

By applying UNIQUE to the range and criteria, we avoid counting duplicates that might otherwise interfere with the match results.

Note:

REGEXMATCH uses RE2 regular expressions. If your search string contains special characters like *+?()|, you’ll need to escape them. For example, searching for Sulphuric Acid \(H2SO4\) will correctly find the item Sulphuric Acid (H2SO4).

Partial Match Multiple Values in a Column: Case-Sensitive and Case-Insensitive

When doing partial matches (e.g., searching for “Battery” instead of “Battery Box”), you might encounter multiple matches. In this case, you can’t simply count matches because multiple rows might match the same criteria.

For partial matching, we use the SEARCH function (case-insensitive) or FIND function (case-sensitive).

Formula #3 (Case-Insensitive Partial Match):

=ArrayFormula(
     LET(
          range, A2:A, criteria, C2:C,
          IF(
               COUNTIF(BYROW(TOCOL(criteria, 1), LAMBDA(r, COUNT(SEARCH(r, range)))), ">0")=COUNTA(criteria),
               "In Stock", "No Stock"
          )
     )
)

Formula #4 (Case-Sensitive Partial Match):

=ArrayFormula(
     LET(
          range, A2:A, criteria, C2:C,
          IF(
               COUNTIF(BYROW(TOCOL(criteria, 1), LAMBDA(r, COUNT(FIND(r, range)))), ">0")=COUNTA(criteria),
               "In Stock", "No Stock"
          )
     )
)

Explanation of Partial Match Formulas:

• SEARCH is case-insensitive, and FIND is case-sensitive.
• BYROW applies the SEARCH or FIND function to each criterion individually.
• We then check if there is at least one match for each criterion.

That’s all! By using these methods, you can efficiently match multiple values in a column in Google Sheets, whether you need case-insensitive, case-sensitive, or partial matches.

Resources

• Filter Groups Which Match at Least One Condition in Google Sheets
• Formula to Find Partial Match in Two Columns in Google Sheets
• Highlight Values in Sheet2 that Match Values in Sheet1 Conditionally
• How to Find the Closest Match in Google Sheets
• How to Match | Extract Nth Word in a Line in Google Sheets
• Match Available and Required Items and Highlight in Google Sheets
• Match Two Columns that Contain Values Not in Any Order Using Regex
• Nearest Match Greater Than or Equal to Search Key in Vlookup in Google Sheets
• Substitute Nth Match of a Delimiter from the End of a String in Google Sheets