Search code examples
arraysgoogle-sheetsfiltergoogle-sheets-formulavlookup

Filter by two conditions + Array Formula alternative in google sheets

I am well aware that FILTER doesn't work with Array Formula. On all of the posts I've seen, people are substituting it by Array Formula + Vlookup. My problem is that I use FILTER on more than one condition.

Example: FILTER(Deliveries!A:A, Deliveries!C:C=A2,Deliveries!B:B=B2)) (This is in a different sheet, where I use filters in both Columns B and C).

This sheet is the repository of a form, and when the form adds rows the formulas do not get copied down to the added rows, hence my effort to turn this into an Array Formula or maybe a query. I'd be equally satisfied with another solution (i.e. something that forced formulas to always follow the addition of new rows).

Thank you very much for your time!

Solution

  • try in row 1:

    ={"header"; ARRAYFORMULA(IFNA(VLOOKUP(A2:A&B2:B, 
 {Deliveries!C:C&Deliveries!B:B, Deliveries!A:A}, 2, 0)))}