请求为Google Sheets中的指定QUERY公式添加ARRAYFORMULA

Hey there! Let's turn your formula into an array-friendly version so it auto-populates across all rows without needing to drag it down. Here are two solid options depending on your needs:

Solution 1: VLOOKUP + FILTER (matches your original formula's behavior) #

This version will behave exactly like your original formula, but automatically apply to every row in column A:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, FILTER('B. Projects New'!$A$3:$H, 'B. Projects New'!$G$3:$G="Plan_294492"), 8, FALSE), "-"))

Quick breakdown:

• ARRAYFORMULA wraps everything to make the formula run for every row in A2:A automatically
• FILTER narrows down your source data to only rows where G equals Plan_294492—this makes the lookup faster and cleaner
• VLOOKUP checks each value in A2:A against the filtered dataset and pulls the corresponding H column value (the 8th column in the filtered A:H range)
• IFNA still catches cases where no match exists, replacing errors with -

Solution 2: QUERY-based array version #

If you want to stick with QUERY instead, this adjusted version works well (best if each value in A2:A has at most one matching row in your source data):

=ARRAYFORMULA(IFNA(QUERY('B. Projects New'!$A$3:$M, "select H where G='Plan_294492' and A matches '"&TEXTJOIN("|", TRUE, A2:A)&"'", 0), "-"))

Notes for this approach:

• TEXTJOIN("|", TRUE, A2:A) takes all non-empty values in A2:A and turns them into a regex pattern (using | as an OR separator)
• The matches keyword in QUERY lets us check if column A matches any of those values from our joined list
• Heads up: If a single A value has multiple matching rows, QUERY will return all of them stacked vertically. If you only want the first match, go with Solution 1.

内容的提问来源于stack exchange，提问作者Nirmal Kumar