Search cell for specific text against table column and if found, return that table column cell value-CodePudding

I am looking to search a cell for specific text against a table column and if found, return the table cell value.

This is what i currently have

=VLOOKUP(LOOKUP([@[Workbook Name]],Table10[List of Workbooks]),Table10[List of Workbooks],1,FALSE) which returns #N/A

What i would like is Col A has string to search, Col B has the formula that searches a table and returns matched cell.

Col A                                                      Col B          
ALPH - Group Monthly -22-01-18-19-12-30.xlsm             Group Monthly
ALPH - Home Audit - DEPTS 1 - -22-01-18-16-10-14.xlsm     DEPTS 1

CodePudding user response：

One way would be :

=INDEX( Table10[List of Workbooks], 
        FILTER( SEQUENCE( ROWS( Table10[List of Workbooks] ) ), 
                ISNUMBER( FIND( Table10[List of Workbooks], [@[Workbook Name]] ) ) ) )

If you want it to be case insensitive:

=INDEX( Table10[List of Workbooks], 
        FILTER( SEQUENCE( ROWS( Table10[List of Workbooks] ) ), 
                ISNUMBER( SEARCH( Table10[List of Workbooks], [@[Workbook Name]] ) ) ) )

If there are multiple matches, it will SPILL. If you don't want that, do:

=INDEX( Table10[List of Workbooks],
        TRANSPOSE( FILTER( SEQUENCE( ROWS( Table10[List of Workbooks] ) ),
                           ISNUMBER( SEARCH( Table10[List of Workbooks], [@[Workbook Name]] ) ) ) ),
        1 )