search and match in Access or SQL

[TomCat]

Hi,

I hope that someone can help me with this problem.
I have attached a workbook showing the issue.

In the workbook you will see 2 sheets (sheet1 & sheet2)
What I tried to do was use an INDEX function (in the "path" column, sheet1) to find the phone number from the "TEL" column (sheet1) in the "File Name" column in sheet2.

As you can see the phone number is present in part of the path name in the "File Name" column in sheet2.

=INDEX(Sheet2!$B$1:$B$20,MATCH(1,SEARCH("*"&B2&"*",Sheet2!$B$1:$B$20),0))

This works fine but not if i have to search 45000 rows. Then the search can take hours or maybe days. Not a good thing.

Does anyone know a way to do this in access or SQL (Ihave uploaded the sheets to Access and SQL db's).

Please could you tell me how to do this?

Thanks and Regards,

Thomas

[DonkeyOte]

This is not answering your specific SQL question but you can use non-array formulae in XL:

Sheet1!D2:
=LOOKUP(2,1/ISNUMBER(SEARCH($B2,Sheet2!$B$1:$B$40000)),Sheet2!$B$1:$B$40000)

This will be quicker...you can also think about using formulae to extract the phone number from the path on Sheet2 and then use INDEX/MATCH on Sheet1 by referencing the Column on Sheet2 that contains the extracted number.

[NBVC]

Related to this thread:

http://www.excelforum.com/excel-gene...2-vlookup.html

This will be quicker...you can also think about using formulae to extract the phone number from the path on Sheet2 and then use INDEX/MATCH on Sheet1 by referencing the Column on Sheet2 that contains the extracted number.

which is what I showed in the referenced thread...