Search Text from List (multiple columns) and check if List words appears in String

Given that I have a list of names (Table1 - all of the columns),

And that I have a free style text field (cell G2:G3).

I'd like to return [cust Name] when the list (cust Name to cust#4), is found in the string.

i.e. in cell G2, notice that 'Adam' (in cell C4) was found in, thus return 'First Adam'.

i.e. in cell G4, notice that 'Mary' (cell B3), 'Jane' (cell C3) and 'JANE, MARY' (cell E4) is found in the text as jane, mary (cell G2). Thus returning 'Mary Jane'

Return Value when string contains values from List.png

In H2 try:

Formula:

=IF($G2="","",INDEX(Table1,ROUNDUP(MIN(IF(ISERR(SEARCH(Table1,$G2)),"",SEQUENCE(ROWS(Table1),COLUMNS(Table1),1,1)))/COLUMNS(Table1),0),1))

Here one way

=FILTER(Table1[cust Name],SIGN(MMULT(IFERROR(SEARCH(Table1,G2),0),SEQUENCE(COLUMNS(Table1)))))

ok here is a new one taking into consideration the blanks in your table:

Formula:

=LET(FUN,IF(ISBLANK(tblContract),"|$|",tblContract),IF($A1="","",INDEX(tblContract,ROUNDUP(MIN(IF(ISERR(SEARCH(FUN,$A1)),"",SEQUENCE(ROWS(tblContract),COLUMNS(tblContract),1,1)))/COLUMNS(tblContract),0),1)))

Glad they work and thanks for the rep