Excel multiple criteria match with partial strings using formulas

Question

I have some data that I would like to assign a description based on the partial strings as those can come with partial and different names. Below is an example of the column I need to add a description next to based on the partial match:

PRODUCT
ARK-78787
7878ARK-7555
BEL*EEER44
eerBEL*/*
fdseebel***

and I have a table like this for the description

Category A  ARK,BAR,CER
Category B  BEL,CEL,DEL

I need to match those multiple strings while checking the partial match but have no luck so far. The formula I'm using is:

=INDEX(F:F,AGGREGATE(15,7,ROW($G$3:$I$3)/(ISNUMBER(SEARCH($G$2:$I$2,B2))),1))

Attaching a Google Sheets file as a reference to have a better picture of this

https://docs.google.com/spreadsheets/d/1QuJ695jd4fcZrVdjHEFZa9T7F5c1Ky2xCCzspu8FAiw/edit?usp=sharing

Any thoughts?

usmanhaq · Accepted Answer · 2020-08-05 07:51:56Z

3

Enter the formula in D2 as an array formula and drag it down

=INDEX($H$1:$H$5,MAX((ROW($I$2:$K$5)*ISNUMBER(SEARCH($I$2:$K$5,A2)))))

answered Aug 5, 2020 at 7:51

usmanhaq

1,5771 gold badge9 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OverflowStacker · Accepted Answer · 2020-08-05 12:07:49Z

1

Based on the provided non-array-formula and sample data, the final formula would look like

=INDEX($H$1:$H$5,AGGREGATE(15,7,(ROW($I$2:$K$5))/(ISNUMBER(SEARCH($I$2:$K$5,A2))),1))

EDIT: set ranges according to the answer of @usmanhaq.

edited Aug 5, 2020 at 12:07

answered Aug 5, 2020 at 8:00

OverflowStacker

1,3381 gold badge11 silver badges19 bronze badges

Collectives™ on Stack Overflow

Excel multiple criteria match with partial strings using formulas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related