Jun 04 2022 01:26 AM
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,
Jun 04 2022 02:38 AM
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.
Jun 04 2022 04:21 AM
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?
Jun 04 2022 05:33 AM
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.
Jun 04 2022 05:56 AM
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.
Jun 04 2022 06:23 AM
Solution=INDEX( Mapping[Mapping],
MATCH( TRUE, ISNUMBER( SEARCH(Mapping[Partial Text], [@[Name/Description]]) ), 0 ) )
Jun 04 2022 06:23 AM
Solution=INDEX( Mapping[Mapping],
MATCH( TRUE, ISNUMBER( SEARCH(Mapping[Partial Text], [@[Name/Description]]) ), 0 ) )