Search a range of columns in a sheet and return the Column header where the search key was found

Question

I am currently doing keyword research with 24,000 keywords in it. Now, I have already grouped the keywords by column in "Sheet A" where the 1st row is the cluster name.

Now, what I want to do is to find a fast and automated way for searching the columns in "Sheet A" and pull the header row where the cluster name is for each individual keyword listed in "Sheet B".

I had this

but it returns a #value error that says:

"Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "m "" at line 1, column 26. Was expecting one of: "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "and" ... "or" ..."

Also, it seems that it'll take me ages to load all query fxns for 24,000 data cells so it'll be inefficient. Any help would be much appreciated. Thanks in advance!

Answer 1

try in D1:

={"Cluster"; ARRAYFORMULA(IFNA(VLOOKUP(C2:C, 
 SPLIT(QUERY(FLATTEN(IF(INDIRECT("Clusters!C2:"&ROWS(Clusters!A:A))="",,
 INDIRECT("Clusters!C2:"&ROWS(Clusters!A:A))&"♠"&Clusters!C1:1)), 
 "where Col1 is not null"), "♠"), 2, 0)))}