Search sheet for multiple matches in a single row

Question

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.

Answer 1

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), ))

Answer 2

I think you'd have to use Vlookup, which isn't quite as convenient as Query. This is what I have so far, but it might need a bit more work to get exactly the same results as query in all cases (owing to the ORDER statement within the query):

=ArrayFormula(if(A2:A="","",vlookup(A2:A&B2:B&C2:C&if(D2:D="","*",D2:D),{Items!A2:A&Items!B2:B&Items!C2:C&Items!D2:D,Items!A2:T},{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21},false)))