Vlookup not finding files with ~ in the cell

[bradjedis]

Greetings,

I have a file that has thousands or rows, and I am looking up data in column C to another tab. Many of the items have the "~" character, and the lookup is not finding a match when I expect to see a match.

example:

Source data has in col D:

site~1.log
site~2.log

Target lookup data has the same data.

Here is the formula: =VLOOKUP(C2,'final Data'!C:C,1,FALSE)

Hope there is a fix

Brad

[HansV]

~ is a wildcard character for searching in Excel. To specify that you want to search for the literal character ~, use ~~. So try

=VLOOKUP(SUBSTITUTE(C2,"~","~~"),'final Data'!C:C,1,FALSE)

[bradjedis]

Hans,

That did the trick. I found another character the backslash "\". I tried the same approach, but that did not work.

Thoughts?

[HansV]

VLOOKUP should find strings that contain \.
Make sure that there is an exact match. For example, if either the lookup value or the first column of the lookup range contains an extra space, the match would fail.

[bradjedis]

Ok I shall evaluate the data...

[bradjedis]

Ha! I do indeed have a mismatch. First character in the string is "D" in the source, and "E" in the target.

I am looking to match directory listings.

Checking to see if the Target data has the same as Source. Is there a way to ignore the first character in a formula?

Representative data:

Source: D:\The list\more
Target: E:\The list\more

[HansV]

Try this:

=INDEX('Final Data'!$C$1:$C$10000, MATCH(SUBSTITUTE(MID(C2, 2, 100), "~", "~~"), MID('Final Data'!$C$1:$C$10000, 2, 100), 0))

[bradjedis]

That did it!

I had actually tried an Index formula, but did not catch the issue with the D vs. E.

Thank you,
Brad