0

I have 2 tables. Sheet 1 is s a list of top brands. Sheet 2 is a long list of brands key phrases which include both top brands and normal brands. On Sheet 2 next to the brand cell (on a new adjacent column) I want to mark 1 if it's a top brand and 0 if not.

I have tried the excel sum-if function but that requires an exact match.

Sheet 1 - TOP BRANDS LIST

+--------------+
|  Top Brands  |
+--------------+
| new look     |
| river island |
| tessuti      |
| boohoo       |
| next         |
| zara         |
+--------------+

Sheet 2 - LIST OF BRANDS

+----------------+
|     Brands     |
+----------------+
| asos pants     |
| asos tshirts   |
| zara           |
| zara pants     |
| zara shorts    |
| boohoo pants   |
| boohoo tshirts |
| next           |
| next clothing  |
| next underwear |
+----------------+

I expect the output to be below. Even if the top brand is mentioned in a key-phrase it should mark 1.

+----------------+----------------+
|     Brands     | Keep or remove |
+----------------+----------------+
| asos pants     |              0 |
| asos tshirts   |              0 |
| zara           |              1 |
| zara pants     |              1 |
| zara shorts    |              1 |
| boohoo pants   |              1 |
| boohoo tshirts |              1 |
| next           |              1 |
| next clothing  |              1 |
| next underwear |              1 |
+----------------+----------------+
BigBen
  • 46,229
  • 7
  • 24
  • 40
HBFreelance
  • 1

1 Answers1

0

Tweaking the approach in this answer:

For demo purposes the formula references ranges on the same sheet, but it should be easy to modify for different sheets.

=--(SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$7,C2)))>0)

enter image description here

BigBen
  • 46,229
  • 7
  • 24
  • 40