If cell contains text from a range of other cells-CodePudding

I am working on a project where the aim is to categorize spending on bank statements based on their description. Bank statements are uploaded onto an excel spreadsheet each month, which then should look at the description of each spend and assign it to a category.

There are 75 spend categories listed out in a row, and under each category there is a list of text against which the description of the spends on the bank statement needs to be matched. If the description contains text from a cell, the function should say which category that particular cell is under.

$K$3:$CG$3 is where I have the names of the categories $K$4:$CG$502 is where I have the list of all the identifying text for each of the categories C is the column in which the descriptions from the bank statements are

I have come up with the below formula, which works fine however if I expand it to the full range of identifiers (replace $K$4:$CG$4with $K$4:$CG$502 in SEARCH function) it fails.

={INDEX($K$3:$CG$3,1,MATCH(TRUE,ISNUMBER(SEARCH($K$4:$CG$4,C6)),0))}

Any help is much appreciated.

The table is found on this

• Formula in cell D3

=INDEX($K$3:$CG$3,
AGGREGATE(15,6,(COLUMN(Control!$K$4:$CG$502)-COLUMN(Control!$K$4) 1)/
(ISNUMBER(SEARCH(Control!$K$4:$CG$502,C3))),1))

And Fill Down! Workbook_Link