Excel Formula to match part of a cell

I have a list of 5000 Numbers in Column A and a list of 60,000 file path names in column B.
Column A is usually 7 digits long.  Column B is a path that will contain an adobe file that has this 7 digit number within the file name but also has other characters in the name of the file.

I need to find the path for the number in cell A and put it in Cell C.

Lot Number      Path
877407      D:\APF\Mtr\2013\0000149-15947-62130915-C6927M-080613D1.pdf
1210535      D:\APF\Mtr\2013\0000149-15947-62130915-C6927M-080813C3.pdf
1437238      D:\APF\Mtr\2013\0000248-10220090-2821398-105995-1517-13.pdf
1437238      D:\APF\Mtr\2013\0000465-5192708-2794107-102168-1243-13.pdf
1456640      D:\APF\Mtr\2013\0000465-5192708-2819472-105901-1491-13.pdf
      D:\APF\Mtr\2011\877407-S52139.pdf
1555377      D:\APF\Mtr\2013\0000465-5192708-2827462-106975-1612-13.pdf
1596955      D:\APF\Mtr\2013\0000702-10229930-2778267-100205-1127-13.pdf
1716108      D:\APF\Mtr\2013\0000960-201206182-073113B4.pdf
1725740      D:\APF\Mtr\2013\0000966-20236030-2805974-104084-1332-13.pdf
1725740      D:\APF\Mtr\2013\0001311-15947-62130915-C6927M-071813B1.pdf
1729494      D:\APF\Mtr\2013\0001311-15947-62130915-C6927M-071913B1.pdf
1732121      D:\APF\Mtr\2013\0001311-201011221-C6619M-072413C2.pdf
1760677      D:\APF\Mtr\2013\0001311-WJ531-C6927M-071813B2.pdf
1766442      D:\APF\Mtr\2013\0001311-WJ531-C6927M-072213A3.pdf
1785229      D:\APF\Mtr\2013\0001647-WJ863-271018-092413C2.pdf
1795172      D:\APF\Mtr\2013\0001890-20270130-2826440-106137-1613-13.pdf
2007034      D:\APF\Mtr\2013\000925-JM3426-005-050313C1.pdf
      D:\APF\Mtr\2013\MTR1555377-0.pdf
2644791      D:\APF\Mtr\2013\1725740-DCJN-794711.pdf
LVL 4
Sue TaylorProject ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

btassureCommented:
Do you need the full path including filename? Or just the containing folder?
Sue TaylorProject ManagerAuthor Commented:
Full path including file name.  This will eventually be imported into a database and files will be linked
btassureCommented:
OK, easiest option is a vlookup and a bit of data moving. I would:
Copy all of column A into column C. You can sort it if this would help you later but isn't essential.
Then we will use vlookup to search for a value in C2 anywhere in your "database" of columns A and B and display it in D2.

The formula to enter in D2 is:
=vlookup(C2,A:B,2,FALSE)

I've attached a sample.

Using "FALSE" in the sort means:
If there are multiple paths that match a single lot number it will return the first path it finds.
If it cannot find an exact match it will return N/A.
Lots-and-Paths.xlsx
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Sue TaylorProject ManagerAuthor Commented:
i don't think this is working.  In your sample spreadsheet, the first lot number (877407) that is in cell A2 or C2, should match up with the path in cell B7.

The lot in A8 or C8 (1555377) should match up with the file path in cell B20

THe rest of them are probably not going to be found as they are in the remaining 60,000 that I didn't include in my original sample.

This is how I tried it earlier so I assumed I needed something that would find a partial match instead of an exact match.
Steven HarrisPresidentCommented:
This is not a VLOOKUP scenario, since the two lists are not relative to each other and are independent.

Using btassure's sheet, try using the following in D2 and copy down:

=INDEX(B2:B21,MATCH("*"&C2&"*",B2:B21,0))

Open in new window


NOTE:  You are going to have issues if the numbers appear more than once, since this will return the first string it finds, as is the case in D2.  B2 and B7 both have 877407 in them.
Lots-and-Paths.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
Here is a VLOOKUP formula using wildcards that will find the path:
=IFERROR(IF(A2="","",VLOOKUP(TEXT(A2,"\*#\*"),B:B,1,FALSE)),"")

If the leading zero of the lot number is truncated in column A, but present in column B path, then you can use:
=IFERROR(VLOOKUP(TEXT(A2,"\*0000000\*"),B:B,1,FALSE),"")

The TEXT function converts a number into text. The \* that you see in the TEXT function puts the wildcard character * both before and after the number in the resulting text, i.e. "*877407*".

The difference between the two formulas is important if the lot number has 6 or fewer digits. Cell A2 in my sample workbook contains 877407, which matches cell B7. But the first formula would also match a column B cell containing 9877407. The second formula will match only a cell containing 0877407.

I show both formulas in the attached workbook.
PathfinderQ28271171.xlsx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.