is it possible to do lookup with cells containing tildes "~"?

[Guest]

Hi there,

I've been using vlookup on my tables and just discovered that the lookup
actually failed when the cell contains one or more tildes "~" (well never
really use a tilde for it's special purpose before).

Is there any way that Excel's lookup can match my titles with tildes as well
? if not then I guess I'll have to replace all my tildes in my cells to other
signs......probably hyphens.

Many Thanks in advance!

Steven Vegeta

 

[Ragdyer]

Try using 2 of them:
" ~~ "
The first tells XL to look for the second.
Same as using the tilde to tell XL to look for the " * " or the " ? ".

 

[Biff]

Are you looking up specifically just the character ~ or ?, like this:

.........D..........E
1......x...........1
2......y...........2
3......~..........3
4......?...........4

Give some examples of what your lookup_values are and what the titles are.

Biff

 

[Guest]

Thanks Biff - I'm matching some titles like

1. New DVD ~ Rocky Series
2. New DVD Boxset 3 discs - First Blood part 1 2 3
3. The lord of the rings ~ The return of the king DVD

I have 2 files (obtained from my colleague) containing these titles - and I
need to get the matching ones.

Many Thanks.
Steven

 

[Biff]

Still need more info.

I'm matching some titles like
1. New DVD ~ Rocky Series
2. New DVD Boxset 3 discs - First Blood part 1 2 3
3. The lord of the rings ~ The return of the king DVD

What does "matching" mean? "Matching" them to what? Do you want to know if
an entry is on 2 different lists?

Biff

 

[Tom Ogilvy]

If you wanted to find

New DVD ~ Rocky Series

in the sheet, using Edit=>Find, you would use

New DVD ~~ Rocky Series

You would put two tilde's in for the search target.

 

[Guest]

Sorry Biff - my case is as follow:

I have 2 files - the first file containing the following lines (selected DVD
titles only), and the other one is a master file which has all titles and
it's stock availability (numbers).

I'm trying to use vlookup to get the stock availability for those selected
DVD titles, but then I found out that vlookup can only return the stock
availability for those titles without a tilde, while I'm 100% sure all titles
in my first file do exist in the master file.

I just did a test and it seems all titles with those wildcards "~" "*" "?"
are not vlookup-able at all......I may ask my colleagues to change the titles
then - if I can't get any workaround (apart form renaming the titles) from
experts like you

Many Thanks.

 

[Guest]

Yes Tom I was able to find the titles using "~~" - but when it comes to
thousands of lines I don't think I can do it manually :-(

Please see my reply to biff for more details....many thanks anyway

 

[Guest]

Use the SUBSTITUTE() function on your values when you lookup for them
=VLOOKUP( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( value, "~", "~~" ), "?", "~?"
), "*", "~*" ), table, FALSE )

 

[Biff]

See the reply by PapaDos. It'll work.

It seems like you are inclined to remove/change all those tildes/special
characters. I think that would be a good idea then a basic Vlookup would be
sufficient.

Biff