Unusual VLookup in 2007

[Kay]

Hello and Happy New Year!

I am working on a special project that requires me to match file names on
two different directories. Some of the file names are in another lanquage
and use accent marks, etc. When I use a Vlookup, it returns no match even if
I can see the file in the other list. I assume it has something to do with
the accent marks, etc. Each file name also has a unique 16 digit number at
the beginning. I hoped I could tell the Vlookup to match the first sixteen
characters. Even though I know it is there, the formula returns NA#. My
formula looks like this: =VLOOKUP(LEFT(D45790,LEN(16)),'X
Drive'!$D$45790:$D$60000,1,FALSE).

 

[Don Guillett]

try this idea
=VLOOKUP("124*",G3:H6,2,0)

 

[Kay]

Don,
Perhaps I am not understanding completely. You want me to use the symbol
equivalent of the accent mark in the formula...? How would that help to find
an exact match of all characters. The string uses a combination of alpha and
numeric characters where the alpha characters have the accent mark.

If you do not mind my asking, why doesn't Excel find these characters
anyway. I have the same problem with a tilda. I was able to replace the
tilda with an underscore, but can't seem to replace the accent mark.

 

[Don Guillett]

I'm saying use the number with a wildcard
if you were looking up 123 in a range that had 123xxxxx the formula I
provided will do it. If something else.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Kay]

Don,

Thanks so much for taking the time to look at this. I have attached the
workbook and sent to the email you provided.

 

[pub]

Hello and Happy New Year!

I am working on a special project that requires me to match file names
on two different directories. Some of the file names are in another
lanquage and use accent marks, etc. When I use a Vlookup, it returns
no match even if I can see the file in the other list. I assume it
has something to do with the accent marks, etc. Each file name also
has a unique 16 digit number at the beginning. I hoped I could tell
the Vlookup to match the first sixteen characters. Even though I know
it is there, the formula returns NA#. My formula looks like this:
=VLOOKUP(LEFT(D45790,LEN(16)),'X Drive'!$D$45790:$D$60000,1,FALSE).

I am not sure about the accent marks...but the 16 digit number at the
beginning might be an issue.
look at your X Drive! D45790:d60000 range...are these real 16 digit numbers
that means the LEFT() function is extracting the 1sy 16 characters as a
"text" string and not fining a match.
the -- or the VALUE() usually solves that problem
copy&paste this

=VLOOKUP(VALUE(LEFT(D45790,LEN(16))),'X Drive'!$D$45790:$D$60000,1,FALSE)

and see if it makes a difference
hope this helps.

 

[pub]

I am not sure about the accent marks...but the 16 digit number at the
beginning might be an issue.
look at your X Drive! D45790:d60000 range...are these real 16 digit
numbers that means the LEFT() function is extracting the 1sy 16
characters as a "text" string and not fining a match.
the -- or the VALUE() usually solves that problem
copy&paste this

=VLOOKUP(VALUE(LEFT(D45790,LEN(16))),'X
Drive'!$D$45790:$D$60000,1,FALSE)

and see if it makes a difference
hope this helps.

oops, you are looking at a 1 column table? on the x drive sheet, i would
extract the unique 16 numbers using the LEFT() function and put it in a
column by itself as a key field.