1

I have the below two (shortened) tables:

A1:A5 - Data table

URL
shop.samsung.com/au/accessories/mobile-accessories
samsung.com/us/amp/us/smartphones/galaxy-s9
samsung.com/us/appliances/electric-ranges
samsung.com/global/business/semiconductor/minisite/SSD/notice.html

E1:F5 - Reference Table

If Folder Contains          Value
mobile-accessories          Mobile Accessories
smartphones                 Mobile Phone
appliances/electric-ranges  Cooking Appliance
appliances/refrigerators    Semiconductor

I'm trying to create a formula that will look at the URL table, check if the string has a partial match in the Reference table's "If Folder Contains" column, and return the Value. For example, if I choose the first link (A2) I would expect the formula to return "Mobile Accessories" since A2 contains "mobile-accessories" in the URL.

The reverse I know how to do (simply do a vlookup with a wildcard before/after), but I can't figure out a way to do this. Any guidance will be appreciated

3
  • Do it by a macro (VBA function).
    – Akina
    Aug 20, 2018 at 8:54
  • Is there a native way to do this without resorting to VBA?
    – Adam Reed
    Aug 20, 2018 at 9:14
  • Maybe it exists... I do not see it now, in a linear form at least. And if exists, it will be expencive because of array formulas, I think...
    – Akina
    Aug 20, 2018 at 9:27

2 Answers 2

2

Just wondering if appliances/refrigerators should map to Semiconductor as you have shown in your example. I though may be business/semiconductor might map to Semiconductor. But that's not an issue.

In this example sample URL data is in A1:A5, mapping table is in E1:F5. Now in B2 put the following formula and press CTRL + SHIFT + ENTER from within the formula bar to create an Array Formula. The formula shall now be enclosed in Curly Braces to indicate that it's an Array Formula.

Drag it down up to the intended rows.

=IFERROR(INDEX($F$2:$F$5,MAX(IF(ISERROR(SEARCH($E$2:$E$5,A2)),0,ROW($E$2:$E$5))-ROW($F$1))),"No Category")

enter image description here

The Outer IFERROR is added only for cosmetic purposes, just to show 'No Category' text if no match is found. If in case your version of EXCEL is before 2007, you can drop it. Instead of "No category" message, formula shall just return error for such instances or alternatively you can also use mix of IF(ISERROR()) construct.

0

To solve the issue yon need helper Column to store Partial Match Strings.

enter image description here

Write this formula in Cell N41 & fill down.

=VLOOKUP($N36,$O36:$O36,1,TRUE)

Adjust cell references in the formula as needed.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .