Vlookup to find URL using wildcard

I have a spreadsheet with two sheets. Sheet1 has a list of URLs (example: "https://link.com/cp/976980-EHVwytJab73noy4H")

Sheet 2 has two columns. Column1 contains a large string of text that has the URL in it. (example:"Pre-Screened Applicant: t 2014-18286 - Test. Full Time. $10. Mon-Fri 1600-0000 AttachAsLinks: trueAttachAsHtmlFile: trueBidirectional: trueSpoofing: trueAttachment0: 340585_test_117248_Resume.docxDear Sample, Please review the attached resume(s) for the candidate(s) listed below: Test-US-Brookhaven-Sample, Test, & Associates/Jacobson  Review this candidate's interview: link.com/cp/976980-EHVwytJab73noy4H  Thank you."

Column2 contains a manager name. I want to do a VLOOKUP in Sheet1 to return the name of the manager based on the URL.

I know you can do a VLOOKUP with wildcards and created this search, it keeps returning "#N/A"

=VLOOKUP("*"&A1&"*",Sheet2!A:B,2,FALSE)

It seems like there is something about the characters in the string before the link that break this. Can anyone help me figure this out?

 

Question Info


Last updated February 27, 2018 Views 468 Applies to:
Answer

1 - Wildcards will not work in VLOOKUP and MATCH if string length is more than 255. Since, your string length is more than 255, hence Wildcard VLOOKUP will fail.

2. Since you search string starts with https:// where web-address in your target string doesn't contain https://, hence VLOOKUP would fail even if string length is <=255

3. Hence, you would need to remove https:// and http:// before looking up.

4. Use following formula. I have given range till 100. You replace 100 as per your requirement.

=INDEX(Sheet2!$B$1:$B$100,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(A1,"https://",""),"http://",""),Sheet2!$A$1:$A$100)),,),0))

Best regards
Vijay Verma

Timezone: UTC+05:30, PST+12:30
Availability Hours (UTC) : 05:00 AM - 06:00 PM

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.