Say I had a list like this.

Group 1     Group 2
Edward, C   Kate, A
Mark, F     Ava, Z

Now, in a different column =IF(COUNTIF('Gender'!$A$2:$B$3, "Kate, A"), "", "") is my current formula. I would like to make the first set of empty speech marks return Group 1 or Group 2 depending what column it is. I still require the COUNTIF because my actual sheet has values which will not be contained in either column.

Thanks.

CodePudding user response：

try:

=INDEX(IFNA(VLOOKUP("Kate, A", SPLIT(FLATTEN(
 IF(Gender!A2:B="",,Gender!A2:B&"×"&Gender!A1:B1)), "×"), 2, 0)))

update:

=INDEX(IFNA(VLOOKUP(A1:A, QUERY(SPLIT(FLATTEN(
 IF(Groups!B2:C="",,Groups!B2:C&"×"&Groups!A1&"×"&Groups!B1:C1), 
 IF(Groups!F2:G="",,Groups!F2:G&"×"&Groups!E1&"×"&Groups!F1:G1)), "×"), 
 "select Col1,max(Col3) where Col2 is not null 
  group by Col1 pivot Col2 label Col1'"&A1&"'"), {2, 3}, 0)))

demo sheet