Match Multiple Values in Google Sheets (An Easy Guide)

At a professional level, spreadsheet searches can get quite complex with the amount of varying data they might have. Thus, we have to look for viable and quick solutions for these searches that even an entry-level employee can handle. Following that train of thought, today, we will look at how to match multiple values in Google Sheets from the same column.


3 Approaches to Match Multiple Values in Google Sheets

1. Match and Return Multiple Values from a Column in Google Sheets

We start with something simple. Here we have a worksheet of Names and their respective Country of origin.

dataset for match multiple values in google sheets

What we want to do here is we want to match each name according to their country of origin and list those names separately.

For this, we simply use the FILTER function. An advantage of the FILTER function, in this case, is that it returns values in an array by default. So, we don’t have to resort to the ARRAYFORMULA.

Our formula:

=FILTER($B$3:$B$12,$C$3:$C$12=F2)

using filter function to match and extract multiple values in google sheets

The Country column, C3:C12, contains out lookup value, and the Name column, B3:B12, contains the matched values that are to be extracted. The drop-down at cell F2 contains the criteria we are looking for.

We have also locked our cell references with absolutes ($) so that the references don’t move as we go down the range with each lookup.

Our method in action:

using filter function to match and extract multiple values in google sheets animated

Read More: Match from Multiple Columns in Google Sheets (2 Ways)


2. Using REGEXMATCH to Lookup Multiple Values in Google Sheets

Having an item inventory is common in spreadsheet applications, and one of the major tasks involving an inventory database is lookup.

One such lookup criteria could be to match multiple conditions and return a value if TRUE.

For example, let’s say we want to know whether Pencils, Erasers, and Sharpeners are available in stock. All three must be available if we want to proceed to the next stage in shipping.

second worksheet for match for multiple values in google sheets

The Formula Breakdown

Since we are working with multiple text matches, the REGEXMATCH function suits this scenario perfectly.

With our three conditions, the format for REGEXMATCH would be:

REGEXMATCH(range,”text_1|text2|text3”)

Obviously, the text conditions will be enclosed within quotation marks (“”). Here we also see the biggest advantage of using REGEXMATCH, which is the ability to use regular expressions to enhance our criteria. We are using the “vertical bar” or “pipe” symbol (|) to add multiple criteria to our match case. This symbol acts as an OR logic.

So, if one of the Items listed in the formula is present in the inventory, it will return 1 (TRUE), otherwise 0 (FALSE):

=ArrayFormula(--REGEXMATCH(B3:B9,"Pencil|Eraser|Sharpener"))

results by regexmatch for multiple values

More than likely, a practical database will have varying ranges of items in the column. So, to accommodate that, we will update our formula to accept an infinitive range. Changing B3:B9 to B3:B.

=ArrayFormula(IF(LEN(B3:B),ArrayFormula(--REGEXMATCH(B3:B,"Pencil|Eraser|Sharpener")),""))

updating the regexmatch formula with infinitive range

Now, for our condition to be TRUE, we must SUM the return values. Since, if the item exists in the inventory, our formula returns 1. So, if all three items are present, the sum value must be equal to or greater than 3.

Thus, our updated (and final) formula is:

=IF(SUM(ARRAYFORMULA(IF(LEN(B3:B),ArrayFormula(--REGEXMATCH(B3:B,"Pencil|Eraser|Sharpener")),"")))>=3,"Available","Not Available")

using regexmatch to match multiple values in google sheets

Note: The text values can be changed to cell references to make the formula more dynamic. This can be useful when looking up different combinations of items.

Read More: Use REGEXMATCH Function for Multiple Criteria in Google Sheets


Similar Readings


3. Using MATCH Function to Match Multiple Values

In our previous section, the core idea to match multiple values in Google Sheets revolved around using the OR logic by taking advantage of a versatile match function, REGEXMATCH.

We can take a similar approach to create a formula that will act as a direct alternative to the previous approach.

We do this by utilizing the MATCH function.

By default, the MATCH function returns the index of the matched value:

=MATCH("Pencil",B3:B,0)

start point of the match function

Us having three match criteria (Pencil, Eraser, and Sharpener) means that we will have three MATCH functions in an OR logic (the functions will be separated by a + symbol).

MATCH("Pencil",B3:B,0)+MATCH("Eraser",B3:B,0)+ MATCH("Sharpener",B3:B,0)

Now, to make sure that all the values exist in our range, we must return a Boolean TRUE. Since we know that if any match occurs, the MATCH function will return a value. And to make sure that all the MATCH functions are turning a value, we enclose them in an AND function.

=AND(MATCH("Pencil",B3:B,0)+MATCH("Eraser",B3:B,0)+ MATCH("Sharpener",B3:B,0))

using match function to match multiple values in google sheets

Now, to send a proper message, we utilize the IF function and finish our formula:

=IF(AND(MATCH("Pencil",B3:B,0)+MATCH("Eraser",B3:B,0)+ MATCH("Sharpener",B3:B,0))>0,"Available")

adding if function to the match formula to output valid message

Now you might be asking: Why haven’t you included a message for the FALSE statement?

That is because, if a value does not exist in the given range, the MATCH function returns an #N/A error instead of FALSE.

we get an error if a value is missing

So instead, we enclose our formula within the error handling function called IFERROR.

=IFERROR(IF(AND(MATCH(“Pencil”,B3:B,0)+MATCH(“Rubber”,B3:B,0)+ MATCH(“Sharpener”,B3:B,0))>0,”Available”),”Not Available”)

using iferror function to catch errors in formula

Read More: Match Multiple Values in Google Sheets (An Easy Guide)


Final Words

That concludes all the ways we can match multiple values in Google Sheets. We hope that our methods come in handy for your spreadsheet tasks.

Feel free to leave any queries or advice you might have for us in the comments section below.


Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo