1

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.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
TC76
  • 860
  • 1
  • 8
  • 17
  • Would it be out of the question to add helper columns to both your Estimate and Item sheets? I think I've got a somewhat [clunky solution](https://i.imgur.com/fPv9q91.png) working, but it requires the addition of some helper columns. – James Jul 06 '19 at 07:39

2 Answers2

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

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
2

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

player0
  • 124,011
  • 12
  • 67
  • 124