Forum Discussion

Frosty_Average's avatar
Frosty_Average
Copper Contributor
Jun 04, 2022
Solved

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 ...
excel
Formulas and Functions
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Jun 04, 2022

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.

Frosty_Average's avatar
Frosty_Average
Copper Contributor
Jun 04, 2022

HansVogelaar 

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?

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 04, 2022

    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.

    • Frosty_Average's avatar
      Frosty_Average
      Copper Contributor
      Jun 04, 2022

      HansVogelaar 

      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. 

       

       

       

Resources