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
ITworksAsked:
Who is Participating?
 
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
0
 
btassureCommented:
Do you need the full path including filename? Or just the containing folder?
0
 
ITworksAuthor Commented:
Full path including file name.  This will eventually be imported into a database and files will be linked
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
0
 
ITworksAuthor 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.
0
 
byundtCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.