Do you know how to match multiple values in a column in Google Sheets? See the following screenshot to understand what is multiple values match in same column in Google Sheets. Column A contain a list of some auto parts. I want to simply check whether few parts are available in this column.
In this Google Sheets, I’ve a list of auto parts especially electrical supply items in Column range A2:A. I’ve an enquiry for three parts – Battery Box, Battery Plate and Battery Cap. But the customer only wants the parts, if all the three parts are available. How to find all the parts are with us. That’s what I’ve said multiple values match in same column in Google Sheets.
How to Match Multiple Values in a Column in Google Sheets
You can depend two different formulas here. I’ll explain both. By using any of the below formulas you can test column A, if the items Battery Box, Battery Plate and Battery Cap are there.
Multiple Values Match in a Single Column Using REGEXMATCH in Google Sheets.
=if(sum(ArrayFormula(if(len(A2:A),ArrayFormula(–regexmatch(A2:A, “Battery Box|Battery Plate|Battery Cap”)),””)))>=3,”In Stock”, “Out of Stock”)
Note: re type all the double quotes. Applicable to all the formulas in this page.
This formula returns “In Stock” if all the three required auto parts are available else “Out of Stock”. How this formula works?
See in detail how the formula develops.
You can use this formula to check the availability of any of the text in cell A1. If Cell A1 value is B, A, or C, the formula returns TRUE, else FALSE. Please note in this formula I’ve used the pipe “|” symbol not backslash.
The above formula is an answer to how to replaces TRUE or FALSE with 1 or 0 in Google Sheets.
Now when you want to use the above formula in a column range, you can make use of the array formula together with it.
=ArrayFormula(–regexmatch(A2:A, “Battery Box|Battery Plate|Battery Cap”))
This formula would return the result as below. But with infinitive ranges, (A2:A is infinitive where as A2:A10 is not) you should use the IF and LEN combo with the above formula as below.
=ArrayFormula(if(len(A2:A),ArrayFormula(–regexmatch(A2:A, “Battery Box|Battery Plate|Battery Cap”)),””))
Here is the result of the above formula.
Now in the final formula what I did’s, just sum the above formula result. If the summed value is greater than or equal to 3, you can say that all the three parts are available.
This’s one of the clean formula to match multiple values in a column in Google Sheets. But this formula has a shortfall. If any of the value in column A repeats, it won’t work correctly. For example I’ve only two of the required auto parts in the list. The third one is not available. But I’ve the repeated entry of available two. In such cases the formula won’t work. This formula may only work correctly, if the items are entered only one time in the column.
The below formula intelligently addresses this issue.
Multiple Values Match in a Single Column Using MATCH Function
=iferror(if(and(MATCH(“Battery Box”,A2:A,0)+MATCH(“Battery Plate”,A2:A,0)+MATCH(“Battery Cap”,A2:A,0))>0,“In Stock”),“Out of Stock”)
This’s another piece of formula to match multiple values in a Column in Google Sheets. As you can see the core part of this formula is the MATCH function.
See in details how I’ve developed the formula.
This match formula returns the relative position of an item in a range. The above formula would return 2 as the item “Battery Box” is in the second row in the range. Same is the case with other two items. If the item “Battery Box” is in not available, the formula would return #N/A error.
We can test weather all of the above items are returning relative position numbers with the help of IF, AND logical test.
=if(and(MATCH(“Battery Box”,A2:A,0)+MATCH(“Battery Plate”,A2:A,0)+MATCH(“Battery Cap”,A2:A,0))>0)
Here I’m adding the relative position of the three auto parts in the range. If any of the item is not available, the formula would return #N/A error. That means if all the three items are available in the list, there would be a value that greater than 0 else #N/A error. If the value is greater than 0, that means I’ve all the required parts in the list in Column A. I’ve used IFERROR function in our master formula to return “Out of Stock” when #N/A error appears.
First apply the above test on a sample sheet with the data as above. Change values and understand how it affect the result. Once you have properly learned how to match multiple values in a Column in Google Sheets, apply on your original set of data.
In real life, you may have parts name in column A and stock (quantities) in column B. In such cases, first filtered out all the items with 0 quantity by using Filter or Query function. Then apply the above formula on the filtered value. That’s all. Enjoy!