Upper Lower Case & Vlookup

[steve case]

Ok, here's what I want to do: Down Column A I have the Alphabet Upper and Lower case and in Column B a corresponding letter for a cypher.

My message "How now Brown Cow" in Row 1 needs to be converted by a the fomulas in row 2, the first formula in the row is show below:

<ul><tt><U>|A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|</U>

|A|D| |H|o|w| |n|o|w| |b|r|o|w|n| |c|o|w|

|a|q| |=VLOOKUP(D1,$A:$B,2,TRUE)|

|B|H|

|b|f|

|Y|O|

|y|k|

|Z|P|

|z|l|

</tt>[/list]

How can I make the formula in [D2] distinguish between upper and lower case?
.
.
.
.
.

 

[mdavidge]

I hope that the Excel image comes through properly below.

The formula you have given delevers #NA error. The formula is written to find the closest value in A to D1 and deliver the corresponding value from B. Is that your intention?

Mike

Book1
	A	B	C	D
1				H
2	a	q		#
3	B	H
4	b	f
5	Y	O
6	y	k
7	Z	P
8	z	i
Sheet2

 

[mdavidge]

Well, the excel sheet is not coming through correctly. However, do you get an error when you put your example into Excel?

Mike

 

[just_jon]

Book1
	A	B	C	D
1	A	q	r	a
2	B	w
3	C	e
4	a	r
5	c	t
6	b	y
7	X	a
8	x	b
9
Sheet3

 

[Yogi Anand]

Hi StACase:

Using the table in Jon's layout, here is another way ...

Book1
	A	B	C	D
1	A	q	r	a
2	B	w
3	C	e
4	a	r
5	c	t
6	b	y
7	X	a
8	x	b
9
Sheet8

formula in cell C1 is ...

=INDEX(B:B,MATCH(1,INDEX(1/(EXACT(D1,A1:A8)),0),0))

 

[steve case]

Thank you Just_John, after I remembered to put in the ($) dollar signs, it copied out across the rest of the row and worked just grand (-:

=LOOKUP(CODE(D1),CODE($A1:$A53),$B1:$B53)


Many thanks to all!
.
.
.
.
.
.