LOOKUP / SEARCH / FIND

[Deano]

Hi

Please could someone help me find the formula that will allow me to identify the group that Wesley and craig belong to in the table below.

I do not want to use VBA for this one.

Thanks Deano

Am having trouble with HTML, so assume this data is in Cells A1:D2

Group A Peter, Wesley, Jane
Group B Paul, Craig

 

[NBVC]

Try:

=INDEX($A$1:$A$2,SUMPRODUCT((ISNUMBER(SEARCH(A7,$B$1:$D$2)))*(ROW($B$1:$D$2))))

where A7 houses the name to find

Confirmed with CTRL+SHIFT+ENTER not just ENTER...you should see {} brackets around the formula if confirmed correctly.

Adjust ranges and reconfirm the formula with the CSE key combo.

 

[Deano]

Excellent, thanks NBVC