Understanding "select X where Y = ..." in a google sheets QUERY function

0
votes

I'm trying to figure out how to parse this google sheets function:

=IFERROR(QUERY($A$2:$F$1000, "select F where A="&A4&" "),"")

I'm having trouble understanding the "select F where A="&A4&" part. The function is applied to an entire column. For some of the rows, this function returns a number, for others it returns a blank. The A column which it is referencing is entirely composed of 6-digit numbers.

What is going on such that sometimes the function returns a number and sometimes a blank?

Also, why are the ampersands important? If I take away the ampersands, the function returns an error.

3
google-sheetsgoogle-sheets-querygoogle-sheets-formula
do you only want to return all the values from F when the value is equal to the value in A4 only? Also your additional &" " after A4 is adding a space after the number that is in A4 - Aurielle Perlmann
@AuriellePerlmann To clarify: the function I included the question resides in the 4th row. There are copies of this function in all the rows of the column, and each function's A corresponds to the row number (&A5&, &A6&, ...) - ioannes

3 Answers

0
votes

You need to fix the quotes around A4.

=IFERROR(QUERY($A$2:$F$1000, "select F where A='"&A4&"'"),"")
0
votes

'"&A4&"' means what is in cell A4 The & means to concatenate. In this case the literal contents of A4 into the query formula.

Notice that the query has 4 "s. ie "" "&"" The single quotes are to make the contents of A4 a string.

where A= so where contents of A2 to A1000 matches the contents of A4. It would definitely match on A4, (and any other Col A cell that had the same contents.)

in which case it would return F4 because of the "select F" means show/return column F in the results

0
votes

You should try the following:

=arrayformula(if(eq(F2:F,A2:A),F2:F,))

It is hard to suggest the right formula without seeing what you are working with or what the expected result looks like, so if this doesn't work, please share your sample spreadsheet.