Google Sheets – How to Return Only the First Row Containing a Value

google sheets

=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.

Best Answer

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.)

Related Topic