Converting a query formula from Vlookup to Hlookup and resolving a "Query completed with an emp-CodePudding

I have put together a formula in GoogleSheets that successfully searches for a specific input from a cell within a column and prints out all of the values that exist across its row in a single cell.

I used Vlookup to reference the values within the column, but now I want to do the same thing, but this time starting from a row (based on my input value) and grab all values within the column and print them out. However, when I switch my formula over from vlookup to hlookup, even though the lookup itself is successful (as I tested that independently), the formula gives me the following error:

"Query completed with an empty output"

Here is my original formula that works followed by my attempt to turn this into an Hlookup across columns that did not work. I'm curious to know what I might be doing wrong:

=TEXTJOIN(CHAR(10) & " ", TRUE, ARRAYFORMULA(IF(QUERY(A3:Z1000,"select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z where A = " &Concat("'",Concat( VLOOKUP(AF3,A3:A1000,1,FALSE),"'"))) <> "", QUERY(A3:Z1000,"select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z where A = " &Concat("'",Concat( VLOOKUP(AF3,A3:A1000,1,FALSE),"'"))), "")))

=TEXTJOIN(CHAR(10) & " ", TRUE, ARRAYFORMULA(IF(QUERY(B2:Z1000,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z where B = " &Concat("'",Concat( HLOOKUP(AF5,B2:Z2,1,FALSE),"'"))) <> "", QUERY(B2:Z1000,"select B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z where B = " &Concat("'",Concat( HLOOKUP(AF5,B2:Z2,1,FALSE),"'"))), "")))

My guess is I need to change something about how the Query works when I am looking up values from a row and want to grab all of the contents of the column?

If anyone has any clues, it would be appreciated.

Cheers,

-~-

I've already setup Concats to make sure the value returns as a string and also ensured all of the data being referenced is formatted as "plain text", but no dice. Again the top example works just fine, so there's something specific I'm missing when attempting to scan from the other direction, I assume.

CodePudding user response：

For Color use below formula-

=TEXTJOIN(CHAR(10),1,FILTER(A3:D,A3:A=G3))

For Type use-

=TEXTJOIN(CHAR(10),1,FILTER(A3:D,A2:D2=G5))

To handle errors use IFERROR() function like-

=IFERROR(TEXTJOIN(CHAR(10),1,FILTER(A3:D,A2:D2=G5)),"")

CodePudding user response：

solution with QUERY:

formula for Color:

=LAMBDA(DATARANGE,SEARCH,
 LAMBDA(SEARCH,
  LAMBDA(RESULT,
   LAMBDA(RESULT,
    JOIN(CHAR(10),RESULT)
   )(QUERY(TRANSPOSE(RESULT),"WHERE Col1 IS NOT NULL"))
  )(QUERY({DATARANGE},"WHERE Col1='"&SEARCH&"'"))
 )(PROPER(LOWER(SEARCH)))
)(A2:D,G3)

formula for Type:

=LAMBDA(DATARANGE,SEARCH,
 LAMBDA(SEARCH,
  LAMBDA(RESULT,
   LAMBDA(RESULT,
    JOIN(CHAR(10),RESULT)
   )(QUERY(TRANSPOSE(RESULT),"WHERE Col1 IS NOT NULL"))
  )(QUERY({TRANSPOSE(DATARANGE)},"WHERE Col1='"&SEARCH&"'"))
 )(PROPER(LOWER(SEARCH)))
)(A2:D,G5)