Vlookup or Match help?

[sherafyk]

Hello, I've been trying to figure out what formula to use so that when I input it in Sheet1 column B, it looks up the string "USERID1" in column A (the url) and matches it with column A in sheet 2, and the result is the value in column B (clientName)

Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]https://example.com/USERID1/randomvalues[/TD]
[TD]=formula for client name[/TD]
[/TR]
[TR]
[TD]https://example.com/USERID1/randomvalues[/TD]
[TD]=formula for client name[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]USERID1[/TD]
[TD]ClientName1[/TD]
[/TR]
[TR]
[TD]USERID2[/TD]
[TD]ClientName2[/TD]
[/TR]
</tbody>[/TABLE]

I'd appreciate any and all help. I've been at this for a few hours with no luck. Thank you!

 

[Aladin Akyurek]

In B2 of Sheet1 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("/"&Sheet2!$A$2:$A$3&"/",$A2),Sheet2!$B$2:$B$3),"")

 

[sherafyk]

Wow, thank you so much. It works perfectly. I was dabbling with IFERROR as well but would never have thought of this. Thanks again!!!

 

[Aladin Akyurek]

Wow, thank you so much. It works perfectly. I was dabbling with IFERROR as well but would never have thought of this. Thanks again!!!

You are welcome.