13 
=QUERY('F/O DEFVSREC'!$1:$1000, "select C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V where B='"&A35&"'label C'', D'', E'', F'', G'', H'', I'', J'', K'', L'', M'', N'', O'', P'', Q'', R'', S'', T'', U'', V''")

There is the formula I am currently using. I have two rows on my F/O DEFvsREC sheet that contains the value i'm looking for. I need to only return the first row containing that value. The row number is 19.

I apologize if i'm asking really simple questions. I'm very new to this.

Improve this question

1 Answer 1

Reset to default
17

To return only one row, use limit 1 in the query; this clause appears before label: 

select C, D, ... where B='"&A35&"' limit 1 label C'', D''...

The default order of the rows returned by query is the order in which they appear in the sheet, so this will result in the first row being returned.

Alternative solution: use filter instead. The formula will be a lot shorter: 

=array_constrain(filter(C:V, B:B = A35), 1, 1e7)

The filter keeps selects columns C through V only from the rows with B equal to the content of A35. Then, array_constrain forces the output to have no more than 1 row and 1e7 (=10000000) columns. (The absurdly large number of columns is just to indicate we don't impose a limit on columns.)

Improve this answer
2
  • I'm inputting data from a separate sheet. So how would I add the sheet key. "F/O DEFVSREC" into this array_constrain filter formula to get it working correctly?
    – gallant03
    May 17, 2016 at 0:15
  • 1
    Put 'F/O DEFVSREC'!C:V instead of C:V, and similarly for B:B. This is just how cell references are made, it doesn't depend on what command you use.
    – user79865
    May 17, 2016 at 0:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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