CODEWITHSUNDEEP
excelexcel-formula
Robert Hall
Robert Hall

Reputation: 191

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

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.

enter image description here

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

Upvotes: 1

Views: 1242

Answers (1)

mark fitzpatrick
mark fitzpatrick

Reputation: 3320

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]] ) ) ) )

enter image description here

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 )

Upvotes: 3

Related Questions