IF statements - 26 combinations

[Rajula]

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

 

[bj]

I think you mean 25 combos (5 squared)
if C1 is 1 through 25
one possibilty is
Set up a range
with A1 values in the first column and B1 values in the first row
and the c1 values at the intersections
=vlookup(A1,Range,match(B1,first_row,0),0)

 

[Ron Rosenfeld]

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

 

[Gary''s Student]

In another column, F enter:

AV
AW
AX
AY
AZ
BV
BW
BX
BY
BZ
CV
CW
CX
CY
CZ
DV
DW
DX
DY
DZ
EV
EW
EX
EY
EZ


then:

=MATCH(A1&B1,F1:F25)

no "if"s required

 

[Rajula]

Hi,

Yes there are 25 combi..
BTW how do i set up a range? Can you be bit more clearer?

 

[Ron Rosenfeld]

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

Sorry, that formula does not work as I expected it to. Please ignore.
--ron

 

[bj]

in this case with a 5 by 5 matrix

use a set of 6 by 6 cells
A11:F16 for example
set
A12="A"
A13= "B"
....
B11="V"
C11="W"
....

 

[Ron Rosenfeld]

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,MATCH(A1,{"A","B","C","D","E"},0)+1)

or, shorter:

=INDEX(Tbl,CODE(UPPER(B2))-84,CODE(UPPER(A1))-63)

--ron

 

[Rajula]

Hi,

I used the MATCH function & its works perfect. Thanks a lot to all those who
replied.

Kind Regards
Rajula

 

[Rick Rothstein \(MVP - VB\)]

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

This simple formula should do what you want...

=5*FIND(A1,"ABCDE")+FIND(B1,"VWXYZ")-5

Rick

 

[Rajula]

Hi Rick,
I used this formula. I am getting the values. The values should be betn 1 & 25
1
16
23
33
46
66
81
88
98
111
121
136
143
153
166
201
216
223
233
246
261
276
283
293
306

=5*FIND(A1,"ABCDE")+FIND(B1,"VWXYZ")-5

 

[Rajula]

Hi Gary,

It works almost fine. There are some problems.
The matrix is like this
V W X Y Z
A 1 2 3 4 5
B 2 4 6 8 10
C 3 6 9 12 15
D 4 8 12 16 20
E 5 10 15 20 25

If A1 is A & B1 is V then C1 = 1 or
If A1 is A & B1 is W then C1 = 2 or
If A1 is B & B1 is V then C1 2.

This makes it slightly complicated.. Any way of solving this?

Regards
Rajula

 

[Rick Rothstein \(MVP - VB\)]

I used this formula. I am getting the values. The values should be betn 1

& 25
1
16
23
33
46
66
81
88
98
111
121
136
143
153
166
201
216
223
233
246
261
276
283
293
306

=5*FIND(A1,"ABCDE")+FIND(B1,"VWXYZ")-5

I don't see how you can be getting numbers like that... the largest value
each FIND function can return, as I set them up, is a number between 1 and
5. So, at maximum for each FIND function, the largest generated value
possible is 5*5+5-5 which is 25. Are you sure you are using my formula with
your A through E letter in cell A1 and your V through Z letter in cell B1?

Rick

 

[Rajula]

Hi,

I set up my matrix like this.
Col F Col G Col H Col I Col J Col K

V W X Y Z
A 1 2 3 4 5
B 2 4 6 8 10
C 3 6 9 12 15
D 4 8 12 16 20
E 5 10 15 20 25

In Col A & Col B i gave the values & in column C i entered the formula
below. But it doesnt work. Sorry..

Col A Col B Col C
A V 1
B V 2

I used the formula like this =VLOOKUP(A2,F2:K7,MATCH(B2,G3,0),0)

Regards
Rajula

 

[Rajula]

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

 

[Rick Rothstein \(MVP - VB\)]

It works almost fine. There are some problems.

The matrix is like this
V W X Y Z
A 1 2 3 4 5
B 2 4 6 8 10
C 3 6 9 12 15
D 4 8 12 16 20
E 5 10 15 20 25

Is that the list of numbers you want us to reproduce for you? Do you really
think we would have been able to deduce that from this explanation from your
first post?

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.

Alright, if those are the values you want, give this formula a try...

=FIND(A1, "ABCDE")*FIND(B1,"VWXYZ")

Rick

 

[Rajula]

Hi Rick,

I used Ron's formula & its working. So i am going to be happy with it for
today.
I will surely find where i went wrong with your formula later.

THanks very much for your help.

Regards
Rajula

 

[Rajula]

Hi,

I tried the find formula as well. Doesnt seem to work.
One of Ron's formula works. Am going to go with it for the moment.

Thanks for for patience & effort.

Regards
Rajula

 

[bj]

change the array in the match portion of your equation and check that you
absolute cell references and relative cell references where appropriate

=VLOOKUP($A1,$F$2:$K$7,MATCH($B2,$F$1:$K$1,0),0)

 

[Rick Rothstein \(MVP - VB\)]

=FIND(A1,"ABCDE")*FIND(B1,"VWXYZ")

I tried the find formula as well. Doesnt seem to work.
One of Ron's formula works. Am going to go with it for the moment.

While I don't understand the problem you had with my first formula in the
other sub-thread, I just wanted to point out this formula is not the same as
that one. My first one produced sequential numbers numbers from 1 to 25. In
this sub-thread, you seem to be saying you don't want that; rather, you
wanted the values according to the matrix you posted. The formula in this
thread should do that for you (remember, it is not the same formula you
tried from my other sub-thread).

Rick