IF statements - 26 combinations
Hi Ron,
The longer formula works. THe shorter one doesnt seems to work.
But for now, finally its work, so i am happy with it. And thanks for all the
effort.
Regards
Rajula
"Ron Rosenfeld" wrote:
On Mon, 25 Jun 2007 08:41:42 -0400, Ron Rosenfeld
wrote:
On Mon, 25 Jun 2007 05:14:00 -0700, Rajula
wrote:
I have to incorporate this in a IF statement or whatever is simple. Does
anybody have a simple solution for this?
Cell A1 can have 5 values A,B,C,D or E
Cell B2 can have 5 values V, W, X, Y or Z
Value of Cell C1 is dependent on A1 & B1
ie IF A1 = A & B1 = V, then C1 = 1
If A1 = A & B1 = W then C1 = 2
If A1 = A & B1 = X then C1 = 3 & so on.... THere are 26 combinations. What
is a easy way of writing this formula
Kind regards
Here's one way, making use of intersection references.
Set up a table with your values, e.g.:
A B C D E
V 1 6 11 16 21
W 2 7 12 17 22
X 3 8 13 18 23
Y 4 9 14 19 24
Z 5 10 15 20 25
Select the table, and then Insert/Names/Create
Check Top Row and Left Column.
Note that when you do this, Excel will change the "C" to "C_" as "C" is not a
valid Name.
Then use this formula:
=INDIRECT(A1&IF(A1="C","_","")&" "&B2)
--ron
The above is incorrect,
But you could set up the table; Name it Tbl, and use this formula:
=INDEX(Tbl,MATCH(B2,{"V";"W";"X";"Y";"Z"},0)+1,MAT CH(A1,{"A","B","C","D","E"},0)+1)
or, shorter:
=INDEX(Tbl,CODE(UPPER(B2))-84,CODE(UPPER(A1))-63)
--ron
|