Forum Discussion
Find substring and bring category
Hello,
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,
=INDEX( Mapping[Mapping], MATCH( TRUE, ISNUMBER( SEARCH(Mapping[Partial Text], [@[Name/Description]]) ), 0 ) )
8 Replies
Try this attachment
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.
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.
