Searching against wildcard or variable length

[rorytm]

Hey,

Im attempting to do a lookup for files, so far I have a batch file that recursively scans the drive and lists files, this outputs to a text file which I have imported into an excel file.

The problem is that the names have wildcards in them...


The file list is in a single column, the longest entry is 13 characters, the shortest 8 and formatted as follows

ABCD100
EFXH500
IJKL6XX

Ideally I would like to search against the wildcards, but I dont think thats possible... What Im attempting to do is to just search against the characters before the wildcard, so, if the lookup value is EFGH, it would search
"EFGH" against "ABCD100", "EF" against "EF", and "EFGH" against "IJKL6" and then return the full "EFXH500" text....

I realize this cant actually find them, but even eliminating some of them from manual look up would be helpful.

(If and of this matters:
The file list is stationary. Column K of a spreadsheet called lookup.xls
The incoming lists are in individual files of varying format, but all have a single column with the values to lookup.
There will never be a wildcard in the incoming list.
The incoming list is actually 20 characters long with TRIM/LEFT.
The X variable is already being changed by a batch script to generate the file list, it can be anything.)

Thanks for any help(or alternate solutions)
Rory


Also... what I thought would work, that does not (=INDEX([Lookup.xls]Local!$K$1:$K$1000,MATCH(TRIM(LEFT(G16,LEN(FIND("X",[Lookup.xls]Local!$K$1:$K$1000,1)))),[Lookup.xls]Local!$K$1:$K$1000,0))

 

[rorytm]

Figured out that the TRIM was pointless, and the LEN was incorrect, and that I missed the second.... but still haven't a clue what to do.


=INDEX([Lookup.xls]Local!$K$1:$K$1000,MATCH(LEFT(G3,FIND("X",[Lookup.xls]Local!$K$1:$K$1000,1)),LEFT([Lookup.xls]Local!$K$1:$K$1000,0)),FIND("X",[Lookup.xls]Local!$K$1:$K$1000,1))

This actually results in empty cells, not an error, and I don't know how to open the error tracing window without an error either.

Any help appreciated,
Thanks
rory