=QUERY('F/O OFF'!1:1000, "select H, J where B='CIN'label H'', J''")

Right now this formula works fine, but i have to change the "where B=" for each row in the sheet. I want to get it so where B= (The value in colA of the row the query is being inputted in)


To replace a fixed string like CIN with the content of a cell, use concatenation &:

=QUERY('F/O OFF'!$1:$1000, "select H, J where B = '"&A2&"' label H'', J''")

As written, this would work for the second row; you can extend the formula to other rows in the usual way, and the relative reference will be adjusted.

  • I've ran into a new problem with this solution. When I extend the formula to other rows in my "1:1000" changes and goes up by one each time. Then for some rows i get an error "Query Completed with an empty output". This is happening as the value im searching for in colb ends up being lower than my starting number. ie the formula says 31:1031 and my value falls on the 23rd row. Is there a way i can adjust the formula so this doesn't happen? Or do i have to do this manually on each row? – gallant03 May 13 '16 at 0:15
  • Use absolute references $1:$1000 or better yet, refer to queried range by columns, because those are the important things. I would describe your range as 'F/O OFF'!B:J because these are the columns you need. Then there are no row limits to worry about. – user79865 May 13 '16 at 0:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.