Partial text lookup between tables

[anemptyroad]

Hi, I've tried searching this forum and Googling for help on this, to no avail. Was hoping someone here might be able to help me out.

I have two tables, Table 1 and Table 3. Table 1 has Page Urls in Column A and the Column B has the Page Type, which is where I'm putting the formula.

Table 3 has my lookup values. Column A has the text parameters for the partial match. All Page URLs will have this unique string in them and I want them to be classified as Column B. I want whatever is in Table 3 Column B to be returned in Table 1 Column B.

Table 1

	A (Page URL)	B (Page Type)
1	www.example.com/products/apples/red	formula here
2	www.example.com/products/apples/green	Products
3	www.example.de/apparel/kiwis/t-shirts	Apparel
4	www.example.co.uk/books/how-to-eat-fruit	Books

<tbody>
</tbody>

Table 3

	A (Partial Text)	B (Return Value)
1	/products	Products
2	/books	Books
3	/accessories	Accessories
4	/apparel	Apparel

<tbody>
</tbody>

So for example, if Table 1 A1 has a page url that says www.example.com/products/apples/red, I want Table 1 B1 to lookup the partial text entries in Table 3, match /products and return Products.

Thanks in advance for your help.

 

[Joyner]

Hello and welcome to the board.

If your data will be the same as your sample, then try this (Where A2 is in your Table1and URLs start in A2, and Table3[All] represents an actual Excel Table, if it is not a table just replace with the Actual Table3 cell references):

=VLOOKUP(MID(A2,FIND("/",A2),FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)),Table3[#All],2,FALSE)

 

[anemptyroad]

Hi, thank you for the response. It didn't work, I'm getting an #N/A error.

Does it matter if Table3 is on the same worksheet and also does it matter if Table3 has headers or not? Both tables are formatted as tables and have headers.

 

[anemptyroad]

Hmm, I think this might be throwing off the formula:

Some of the cells with the URLs have Example Domain instead of just Example Domain. Are those two extra slashes // after http: causing the formula to not work?

 

[anemptyroad]

Sorry, cannot find a way to edit my previous message. It should say:

Some of the cells with the URLS have http://www. example.com instead of just www. example.com. (Added the extra spaces to avoid the hyperlink formatting problem.)

Are those two extra slashes // after http: causing the formula to not work?

 

[anemptyroad]

An update:

So the formula wasn't working when the page URL was http://www_example_com (replaced periods with slashes to avoid linking formatting). The examples I gave were for www_example_com, but most cells will have an http:// in it.

When I removed http:// from the page URL cells, the formula worked. Is there a way to append the formula to account for http://?

 

[Joyner]

Hello, Try this. I know there will be a shorter formula but I'll have to put that together later:

=VLOOKUP(MID(SUBSTITUTE(A2,"http://",""),FIND("/",TRIM(SUBSTITUTE(A2,"http://",""))),FIND("/",SUBSTITUTE(A2,"http://",""),FIND("/",SUBSTITUTE(A2,"http://",""))+1)-FIND("/",SUBSTITUTE(A2,"http://",""))),Table3[#All],2,FALSE)

This won't account for things like "https//" if you will have those.