# Upper Lower Case & Vlookup

#### steve case

##### Well-known Member
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?
.
.
.
.
.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
ABCD
1H
2aq#
3BH
4bf
5YO
6yk
7ZP
8zi
Sheet2

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

Mike

Book1
ABCD
1Aqra
2Bw
3Ce
4ar
5ct
6by
7Xa
8xb
9
Sheet3

Hi StACase:

Using the table in Jon's layout, here is another way ...
Book1
ABCD
1Aqra
2Bw
3Ce
4ar
5ct
6by
7Xa
8xb
9
Sheet8

formula in cell C1 is ...

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

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!
.
.
.
.
.
.

Replies
10
Views
229
Replies
1
Views
108
Replies
6
Views
116
Replies
11
Views
371
Replies
7
Views
139

1,196,322
Messages
6,014,636
Members
441,832
Latest member
tony tessman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back