First of all let me confess that it was a tough time for me to find a suitable title for this Google Sheet tutorial. The reason, the title can be misleading as there are different data structures and different level of comparisons. So let me explain what I meant by Auto Fill Cell with Matching Multiple Conditions.
Check the below data and try to understand what we are going to achieve. I want to auto fill the “Price” column while entering values under “Product”, “Division” and “Sub Division”. I mean a price matching with the data you just entered right under the columns “Product”, “Division” and “Sub Division”. The price is different for each Product and varies when “Division” and “Sub Division” got changes.
I have the “Price Table” on another sheet as below. I will populate the “Price” from the data in this sheet to “Sales Report” sheet and that under “Price” column. To do that there is a comparison required. We compare data in both sheets using a formula to get our desired result.
You can do this with a simple formula in Google Sheets. You can auto populate values in cells by following the below steps using the filter formula.
How to Auto Fill Cell with Matching Multiple Conditions
Syntax: FILTER(range, condition1, [condition2, …])
Below is our formula to auto fill Cell with matching multiple conditions in Google Spreadsheet. This should be applied to Cell D2 in “Sales Report” Sheet then copy paste right below cells to auto populate the value. Don’t forget to apply the proper Dollar symbols in formula before copy and paste.
=filter(‘Price Table’!D2:D9,’Price Table’!A2:A9=A2,’Price Table’!B2:B9=B2,’Price Table’!C2:C9=C2)
Before copy and paste the above filter formula, apply the Dollar symbols as below.
=filter(‘Price Table’!$D$2:$D$9,’Price Table’!$A$2:$A$9=$A2,’Price Table’!$B$2:$B$9=$B2,’Price Table’!$C$2:$C$9=$C2)
Now I think we have reached the point to explain the above formula. Here what we did is, we filtered column “D” in sheet “Price Table”. It’s the rage as per the Filter function Syntax.
As per the function syntax, we can apply multiple conditions in Google Sheet Filter formula and we used only three conditions here. We matched the following “Price Table” sheet columns and they are “Product”, “Division” and “Sub Division”, with values in cells under equivalent columns in the “Sales Report” sheet. That’s all. Hope you understand how can you auto fill Cells with matching multiple conditions in Google Sheet.
The tutorial will be quiet easy if you create same sample data in a Spreadsheet and apply the formula as it is. Take time to understand this powerful filter function. I’m sure you will find it useful in the future. So learn it today.