W

#### Walter Briscoe

I have an application in which I want to map non-negative integers to

text. I tried using OFFSET.

A B C D E

1 Clubs Dimonds Hearts Spades NoTrump

2 0 Clubs

B2 is =OFFSET($A$1,0,$A$2,1,1)

I use 0 for cols to show the problem with the next part.

If B3 is =OFFSET({"Clubs"},0,$A$2,1,1), I hoped to do something similar

with a constant array but get "The formula you typed contains an error".

I accepted the error that Excel gives me because OFFSET help says:

"OFFSET(reference,rows,cols,height,width)

Reference is the reference from which you want to base the offset.

Reference must refer to a cell or range of adjacent cells; otherwise,

OFFSET returns the #VALUE! error value."

It seems, Excel does not accept an array constant as equivalent to a

cell or range of cells in an OFFSET call. I think it should as "About

array formulas and array constants" help says "Array constants can be

used in place of references when you don't want to enter each constant

value in a separate cell on the worksheet".

I am looking for a better way of including array constants within

formulas, rather than in cells.

I don't like

=IF($A$2=0,"Clubs",IF($A$2=1,"Dimonds",IF($A$2=2,"Hearts",IF($A$2=3,"Spa

des","NoTrump"))))

or

=IF($A$2<2,IF(A2=0,"Clubs","Dimonds"),IF($A$2<4,IF($A$2=2,"Hearts","Spad

es"),"NoTrump"))

and I don't want a user-defined function (UDF) either.

I am looking for some simple means to get a member from a constant array

in a worksheet. e.g. something like ={"Clubs"}(0) or ={"Clubs"}(1).