map a range of numbers to a range of corresponding letters?

[Pokey]

I need help constructing a formula to map a range of numbers to a
corresponding range of letters like below.

Each number is a unqie cell, and each letter is in a unique cell.

INPUTS

1 2 3 4 5 6
7 8 9 10 11 12

A B C D E F
G H I J K L


Ideally I would like to write a formula that I can copy straight down a
column that would allow me to enter a number in one column and then
give me the corresponding letter to the right.

For example I would enter:

4 and the formula would produce D, and so on.

11 K
8 H


I've been toying with OFFSET, but I would like to try to avoid a series
of nested "IF" statements b/c of the potential size of my ranges, plus
it just doesn't scale well.

Any help much appreciated.

 

[Earl Kiosterud]

=CHAR(A1+64)

This will work beyond L.

=CHOOSE(A1,"A","B","C","D", ... )

Both will do wierd stuff with an empty cell. We can fix that.

 

[Pokey]

Many thanks for the prompt response.

This assumes that #1 will always be "A" and so on. Ultimatley my array
will be in excess of 100, so I've built a series of nested IF
statements that do the necessary math for 26 letter alphabet, and then
combined that with an "&" to get 27 to = "AA" and so on. I believe
this will work even though I am not crazy about nested IFs. The nice
thing is that this is all one-time set up. Of course, if for some
reason I decide to change my mapping, then I suppose I could always use
CHOOSE along with a similar "suffix" to the formula as I've done above.

 

[Sandy Mann]

This Chip Pearson Function from 2001 will return a Column name from a number
so it will work up to IV:

Function ColumnLetter(ColNum As Integer) As String
ColumnLetter = Left(Cells(1, ColNum).Address(True, False), 1 - (ColNum >
26))
End Function

It will return the letter, or double letters, corresponding to a number, so
if cell C5 has 100 in it then

=columnletter(C5)

Will give you CV


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Earl Kiosterud]

Pokey,

It'd be much easier if you made a simple table by putting the numbers in a
single column, and the corresponding letters in a column to the right:

1 A
2 B
3 C
4 D etc.

then used something like

=VLOOKUP(cell, A2:B103, 2, FALSE)

where cell is the cell reference of the cell containing the number. THe
table could be in another sheet, and could be hidden.

 

[Gord Dibben]

Using Earl's Vlookup suggestion you can enter this in B2 and copy down to get
letters past Z......AA, AB etc to IV

=SUBSTITUTE(ADDRESS(1,ROW(A1),4),"1","")


Gord Dibben MS Excel MVP

 

[Pokey]

All, appreciate the resposnes.

Unfortunately, I can't drop the source data in a column like suggested
above or I would have used the VLOOKUP function.

 

[Roger Govier]

Hi

Then to obtain the result for any number entered in a cell, let's assume
you used cell A4, amend Gord's formula to
=SUBSTITUTE(ADDRESS(1,A4,4),"1","")

No Vlookup's involved it just returns the letter corresponding to any
numeric value in cell A4