Google sheets query – I would like to return a column, but if cells in the column are empty return cells in another column instead

Kinda new at this, struggling with a solution.

So currently my formula looks like this:

=Transpose(Query(importrange("1XlxiJwGNEEgeV7qAbN4QXR1vustupeUMs5tOLI8qm54","2020 LNA!A:K"), "select Col8 where Col2 contains '"&P136&"' AND Col8 is not null offset 1"))

Col2 is my criteria and remains constant. What I’m looking to do is have the formula return the cell in Col9 if the cell in Col8 is empty. If the cell in Col8 is not empty it will just return as is.

Col8 is not null is to prevent returning empty spillover rows after the data.

Ideally I would use the case function but that isn’t present for Sheets. Would love to hear any input, do let me know if more info/clarity is needed as well.

EDIT: Added pictures for reference. Image 1 is a separate workbook, Image 2 is desired output (E2:J2) with D1 as criteria

enter image description here

Answer

try:

=TRANSPOSE(QUERY(FLATTEN(QUERY(IMPORTRANGE(
 "URL_or_ID_here", "2020 LNA!A2:C"), 
 "select Col2,Col3 where Col1 = '"&A1&"'", 0)), 
 "where Col1 is not null", 0))

Advertisement

Answer