Fuzzy Logic? Partial match to retrieve values from separate table

[maxormark]

I have two sheets in Excel 2013: One contains more than 200 Brandnames, plus their importance on an increasing scale starting at 1, plus a category. The second sheetcontains more than 13,000 keyword phrases, which may – or may not – contain the Brandnames in the first table.

What I want to do is to use a sort of VLOOKUP() statement to search Sheet 1 for each of the 13,000 keywords in Sheet 2 and – if it finds a partial match – return the Importance and Category.

For example ...

Keyword = "Ralph Lauren kids";
Brand = "Ralph Lauren"​

Brand-Importance = "124"
Brand-Category = "Fashion"
​

Result for "Ralph Lauren kids"​

Keyword-Importance = "124"
Keyword-Category = "Fashion"
​

Because the actual numbers of results are too large to show here, I've created a test book to show the actual problem.

Sheet1

Sheet2

In a perfect world, the end result should be ...

Sheet2

I found what I believed was a solution elsewhere on this forum; however, despite modifying the formula given there for Sheet2/C7 to read ...

=IF(ISNA(LOOKUP(10^10,FIND(Sheet2!$A$1:$C$17,A7),Sheet2!$B$2:$B$6)),"",LOOKUP(10^10,FIND(Sheet2!$A$2:$A$6,A7),Sheet2!$B$2:$B$6))

... it still doesn't work.

I've also run it past our office Excel guru and he's stumped too, so I'm wondering if this really is a solution after all.

I'd be grateful for any help. I could do it manually, but 13,000 lines of updates is a very long weekend.

Thanks in advance,

m

 

[Aladin Akyurek]

Would you post the samples in a form that is readable directly into Excel? Images don't afford that action.

 

[Mogthra]

This Youtube series teaches you how to make your own search engine. I've used this methodology for many projects and I think it could help you out...https://www.youtube.com/watch?v=jzs3QYj9EDg&index=2&list=PL18CB8BD71E932861

 

[István Hirsch]

Give these formulas a try, enter them with Shift-Ctrl-Enter in

F2:
=IFERROR(INDEX($B$2:$B$17,MATCH(TRUE,ISNUMBER(SEARCH($A$2:$A$17,E2)),0)),"")

G2:
=IFERROR(INDEX($C$2:$C$17,MATCH(TRUE,ISNUMBER(SEARCH($A$2:$A$17,E2)),0)),"")

Excel Workbook
	A	B	C	D	E	F	G
1	Brand	BR	Category		Keyword	BR	Category
2	Nike	1	Sport		kids designer clothes
3	Adidas	2	Sport		Lacoste	5	Sport
4	Stella Mccartney Kids	3	Fashion		ralph lauren	5	Fashion
5	Bogner	3	Sport		petit bateau
6	Mayoral	4	Fashion		kids clothes
7	Poivre Blanc	4	Sport		ralph lauren kids	5	Fashion
8	Ralph Lauren	5	Fashion		Poivre Blanc	4	Sport
9	Lacoste	5	Sport
10	Ugg	6	Fashion
11	Boss	7	Fashion
12	Nike Golf	61	Sport
13	Globe	62	Sport
14	Oakley	63	Sport
15	Giro	64	Sport
16	Ariat	65	Sport
17	Atletico Madrid	66	Sport
Sheet

 

[Aladin Akyurek]

Would you post the samples in a form that is readable directly into Excel? Images don't afford that action.

Here is one method: Borders-Copy-Paste, if you are given the chance of course.

Sheet2, B2, just enter, copy across to C2, and down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Sheet1!$A$2:$A$17,$A2),Sheet1!B$2:B$17),"")

 

[maxormark]

This works perfectly. And I've been able to slot it seamlessley into the bigger project too.

Many thx

m