Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-vlookup

Search sheet for multiple matches in a single row

I would like to modify the following QUERY, or some formula variation that accomplishes the same result, to only be entered a single time at the top of the column and fill the cells below using ARRAYFORMULA

=IFERROR(QUERY(Items!$A$2:$T,"SELECT E,F,G,H,I,J,K,L,M,N,O,P,Q,R where A = '"&Estimate!A2&"' and B = '"&Estimate!B2&"' and C = '"&Estimate!C2&"' "&if(Estimate!D2<>"", "and D = '"&Estimate!D2&"'",)&" AND A is not null LIMIT 1", 0),"")

My formula is in Estimate!G2 of my spreadsheet.

Solution

  • this answer is pretty much correct, tho the exact formula would be:

    =ARRAYFORMULA(IF(LEN(A2:A), VLOOKUP(A2:A&B2:B&C2:C&IF(LEN(D2:D), D2:D, "*"), 
 {Items!A2:A&Items!B2:B&Items!C2:C&Items!D2:D,Items!A2:T}, 
 {6,7,8,9,10,11,12,13,14,15,16,17,18,19}, 0), ))

    0