CODEWITHSUNDEEP
excelexcel-formula
d1ch0t0my
d1ch0t0my

Reputation: 443

Using Index/Match on external sheet with delimited cells

I have a spreadsheet, Alpha that references an external spreadsheet Bravo.

I need Alpha's B2 cell to check in Bravo for all cells in column A that contain the string in Alpha A2. If it finds a match it copies the value from the B column for that row to Alpha B2. I've currently got this working using the following formula:

=INDEX([Bravo.xlsb]Sheet1!A$2:B$22,MATCH(A2,[Bravo.xlsb]Sheet1!$A$2:$A$22,0),2)

..however, I have some cells in Bravo that have multiple strings separated by delimiter that need checking. To complicate matters some of the reference cells in Alpha's A column have multiple strings separated by delimiter. Ideally the images below show how I need this formula to work:

Alpha.csv:

bravo.xlsb

Bravo.csv:

alpha.xlsb example

So my question is, how can I modify the formula to work with cells that contain delimitered strings as well?

Update

To clarify this is how Alpha looks before any formula is run:

Alpha.csv (pre-formula)                             

Items                                   Category Group (results)
Oranges|Chicken                         
Ice Cream|Pears|Steaks

...and this is how Bravo looks 

Bravo.csv

Item Categories                         Category Group
Fruits>Pears|Fruit>Oranges              Fruits & Health
Meat>Steaks|Meat>Chicken|Meat>Lamb      Meats
Deserts>Ice cream                       Deserts & Sweets

I need B2 of Alpha to take each string in A2 (separated sometimes by delimiter for multiple strings) check through Bravo A column for a match for each string. If it finds a match it adds the corresponding category Group name from Bravo's B column to the Alpha B2 cell.

It repeats this for each string in A2 and if there are multiple strings adds in a | delimiter until all strings have been checked. The result would look like this:

Alpha.csv (post-formula)                                

Items                                   Category Group (results)
Oranges|Chicken                         Fruits & Health|Meats
Ice Cream|Pears|Steaks                  Deserts & Sweets|Fruits & Health|Meats

Upvotes: 1

Views: 435

Answers (1)

Melk
Melk

Reputation: 37

One option would be to simply use the Text-to-Columns feature to delimit your list in your Alpha document. That will allow you to use this formula:

=INDEX([Bravo.xlsx]Sheet1!$B:$B,MATCH("*"&A2&"*",[Bravo.xlsx]Sheet1!$A:$A,0))

This formula looks for the value in Alpha A2 with any values before it and any values after it within column A in Bravo.

Otherwise you would have to be more specific on what you which category you would like to search, in which case you can utilize right() mid() and left() to adjust your match() function.

Upvotes: 2

Related Questions