SOLVED

Find substring and bring category

Copper Contributor

Hello,

I would like to map the following situation:

 

There is table 1 with categories based on store names 

Frosty_Average_0-1654330920049.png

 and there is also table 2 with transactions  

Frosty_Average_1-1654330951888.png

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

@Frosty_Average 

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?

@Frosty_Average 

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 :

Frosty_Average_0-1654347162147.png

 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. 

Frosty_Average_2-1654347304732.png

 

 

 

best response confirmed by Frosty_Average (Copper Contributor)
Solution

@Frosty_Average 

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

@Frosty_Average , you are welcome

1 best response

Accepted Solutions
best response confirmed by Frosty_Average (Copper Contributor)
Solution

@Frosty_Average 

=INDEX( Mapping[Mapping],
  MATCH( TRUE, ISNUMBER( SEARCH(Mapping[Partial Text], [@[Name/Description]]) ), 0 ) )

View solution in original post