CODEWITHSUNDEEP
excelexcel-formula
David G
David G

Reputation: 43

How do i do a index match split and concatenate multipul IDs in a cell in google sheets?

Hey all please help me i need your help with something i been breaking my head on

basically lets say in google sheets i have a list of products and each product has an ID

when making me sheet i have boxes that contain more than 1 product so in a single cell there is for this case 3 different ID codes displayed like this (eg: 087345-080046-083823)

i need to some how in the column in another sheet state what the products are for (eg:Shirts-shoes-shorts) so whet im looking for that i cant find anywhere is a formula like

=Index(split()match()concatenate())

Thanks.

Upvotes: 0

Views: 533

Answers (2)

user11982798
user11982798

Reputation: 1908

You said that in google sheets you have a list of products, so below is formula in google sheet. Assumption data in E1:E2 and master in A2:B4, then:

= arrayformula
  ( substitute
    ( transpose
      ( query
        ( transpose
          ( vlookup
            ( split ("'" & substitute(E1:E2,"-","-'"),"-",true,true) ,
              ProdMaster!A2:B4,2,false
            ) & "-"
          ),"Select *",1000
        ) & "%"
      ),"-%",""
    )
  )

Upvotes: 0

bosco_yip
bosco_yip

Reputation: 3802

This formula solution work in Excel sheet for the Office 365 version due to the using of TEXTJOIN function.

In E2, enter array formula (Ctrl+Shift+Enter) :

=TEXTJOIN("-",1,IF(ISNUMBER(MATCH(A2:A10,0&FILTERXML("<a><b>"&SUBSTITUTE(D2,"-","</b><b>")&"</b></a>","//b"),0)),B2:B10,""))

enter image description here

Upvotes: 2

Related Questions