Find substring and bring category

New Contributor


I would like to map the following situation:


There is table 1 with categories based on store names 


 and there is also table 2 with transactions  


I would like to bring the mapping for each transaction but the store name is different. 

Do you have any clues how to match substrings/ partial text so I can bring the categories?


Kind Regards,


8 Replies


In the first Mapping cell:


=IFERROR(INDEX(Table1[Mapping],MATCH(TRUE,ISNUMBER(SEARCH(Table1[Partial Text],[@Store])),0)),"")


If necessary, replace Table1 with the real name of the first table.

@Hans Vogelaar 

Hello and thank you very much for your reply. 

I tried =IFERROR(INDEX(mapping, MATCH(TRUE,ISNUMBER(SEARCH(mapping[Partial Text],transactions[#All])),0)),"") but the outcome is 0. 

I imagine my steps would be :

1. find any text like lidl or kaufland from mapping in table 2 (store/transactions) and if find

2.bring index from mapping and based on that => category.

But I am afraid that search can't work on table to table..  just cell against cell..  what do you think?


I don't understand your formula. Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

Try this attachment

@Hans Vogelaar 

I see that I cant upload excels in here. 

So I have table mapping :


 and table transactions - where I want to bring the mapping with formulas, not manually. Buuut I do not have a common key, just partial text. 





best response confirmed by Frosty_Average (New Contributor)


=INDEX( Mapping[Mapping],
  MATCH( TRUE, ISNUMBER( SEARCH(Mapping[Partial Text], [@[Name/Description]]) ), 0 ) )
Thank you very much!

@Frosty_Average , you are welcome