INDEX-MATCH für teilweise Übereinstimmung der Referenzzeile mit mehreren Nachschlagewerten

Question

Ich habe die folgenden zwei (gekürzten) Tabellen:

A1:A5 - Datentabelle

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 - Referenztabelle

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

Ich versuche, eine Formel zu erstellen, die die URL-Tabelle durchsucht, prüft, ob die Zeichenfolge eine teilweise Übereinstimmung in der Spalte „Wenn Ordner enthält“ der Referenztabelle aufweist, und den Wert zurückgibt. Wenn ich beispielsweise den ersten Link (A2) wähle, erwarte ich, dass die Formel „Mobile Accessories“ zurückgibt, da A2 „mobile-accessories“ in der URL enthält.

Ich weiß, wie man es umgekehrt macht (einfach eine vlookup-Suche mit einem Platzhalter davor/danach durchführen), aber ich kann keinen Weg finden, dies zu tun. Für jede Anleitung bin ich dankbar.

Answer 1

Ich frage mich nur, obHaushaltsgeräte/Kühlschränkesollte zugeordnet werden zuHalbleiterwie Sie in Ihrem Beispiel gezeigt haben. Ich bin vielleichtGeschäft/Halbleiterkönnte abgebildet werden aufHalbleiterAber das ist kein Problem.

In diesem Beispiel befinden sich die Beispiel-URL-Daten in A1:A5, die Zuordnungstabelle in E1:F5. Geben Sie nun in B2 die folgende Formel ein und drücken Sie in der Formelleiste CTRL+ SHIFT+ ENTER, um eine Array-Formel zu erstellen. Die Formel muss nun in geschweifte Klammern eingeschlossen werden, um anzuzeigen, dass es sich um eine Array-Formel handelt.

Ziehen Sie es nach unten bis zu den gewünschten Zeilen.

=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")

Der äußere IFERROR wird nur aus kosmetischen Gründen hinzugefügt, um den Text „Keine Kategorie“ anzuzeigen, wenn keine Übereinstimmung gefunden wird. Falls Ihre EXCEL-Version vor 2007 liegt, können Sie ihn weglassen. Anstelle der Meldung „Keine Kategorie“ gibt die Formel in solchen Fällen einfach einen Fehler zurück, oder Sie können alternativ auch eine Mischung aus IF(ISERROR())-Konstrukten verwenden.

Answer 2

Um das Problem zu lösen, benötigen Sie eine Hilfsspalte zum Speichern teilweiser Übereinstimmungszeichenfolgen.

Schreiben Sie diese Formel in die Zelle N41und füllen Sie sie aus.

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

Passen Sie die Zellbezüge in der Formel nach Bedarf an.