0

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.

Rubén
  • 34,714
  • 9
  • 70
  • 166
ioannes
  • 73
  • 1
  • 9
  • 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 Jan 26 '16 at 03:06
  • @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 Jan 26 '16 at 03:14

3 Answers3

0

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.

double-beep
  • 5,031
  • 17
  • 33
  • 41
Aurielle Perlmann
  • 5,323
  • 1
  • 15
  • 26
0

You need to fix the quotes around A4.

=IFERROR(QUERY($A$2:$F$1000, "select F where A='"&A4&"'"),"")
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
0

'"&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

DaftVader
  • 105
  • 1
  • 11