Excel 2003 vlookup Query 1

[mearmortal]

Ive created a list of two columns, the first column lists 0-9, space, a-z and A-Z. Sorted the list using case sensitive so you get 0-9, space, a A b B c C, etc. I then added the second column with similar numbers/letters etc but substituted letters like a with 4 and L with 1. Like you would do with password creations.
The vloopup matches a case of character say 'a' with the one in the first column and substitues the one on the second column right.
(VLOOKUP(C$5,$N:$O,2,FALSE)
C$5 contains 'M' and
$N:$O contains the lookup values

So why is it the returned value is 'm' not 'M' why is the selection not case sensitive, can I adjust it so its case specific.?

I'm looking forward to the reply

 

[SkipVought]

Hi,

Rather than explain what is in each column, please post an example of your lookup data.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[mearmortal]

As Requested Sample Data: - The first column of data is in 'N' and the second is in 'O'.

0 0
1 L
2 2
3 3
4 a
5 S
6 G
7 7
8 *
9 9
!
a 4
A @
b b
B B
c c
C C
d d
D D
e e
E 3
f f
F F
g g
G G
h h
H H
i i
I I
j j
J J
k k
K K
l l
L 1
m m
M M
n n
N N

 

[cheerio]

VLOOKUP is NOT case sensitive.
You have to work with what Microsoft gives you.

For this task I would use the CODE function to turn the characters into ANSI codes.

I would point the vlookup at the Codes.

eg

You want to map A - its code is 65
Lookup against 65 what the encrypted answer is.

If you want to map a then its code is 97 so you get a different answer.

 

[SkipVought]

make this in column P
[tt]
=CODE(N1)
[/tt]
copy it to column Q and then copy both down.

These are the ASCII codes for the characters.

Here is your lookup
[tt]
=Char(VLOOKUP(CODE(C$5),$P:$Q,2,FALSE))
[/tt]


Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[mearmortal]

Nice one SkipVought works a treat, now the fun starts.

Thanks very much.

 

[SkipVought]

Actually, you don't even need to add the two extra columns (P & Q) with codes

Just enter this as an ARRAY formula ctr+shift+ENTER...
[tt]
=CHAR(VLOOKUP(code(C$5),code($N:$O),2,FALSE))
[/tt]


Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}