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

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)


"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

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

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
--
Gary''s Student - gsnu200733


"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

Hi,

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

"bj" wrote:

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)


"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

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

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

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

"Rajula" wrote:

Hi,

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

"bj" wrote:

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)


"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

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

Hi,

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

Kind Regards
Rajula


"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"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

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

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

"Rick Rothstein (MVP - VB)" 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

This simple formula should do what you want...

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

Rick

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


"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200733


"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

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

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

"bj" wrote:

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

"Rajula" wrote:

Hi,

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

"bj" wrote:

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)


"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

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

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

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

"Rick Rothstein (MVP - VB)" wrote:

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

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


"Rick Rothstein (MVP - VB)" wrote:

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

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)

"Rajula" wrote:

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

"bj" wrote:

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

"Rajula" wrote:

Hi,

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

"bj" wrote:

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)


"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

=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

On Mon, 25 Jun 2007 08:34:01 -0700, Rajula
wrote:

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

I'm surprised the shorter formula doesn't work. And, if you wish, would be
happy to try to figure out why. But, so long as you have a formula that's
working, that may be all that is required.

Thanks for the feedback.
--ron

Hi

If the only values that will be within A1 are ABCDE, and in B1 VWXYZ,
then
=(CODE(A1)-64)*(CODE(B1)-85)

--
Regards

Roger Govier


"Rajula" wrote in message
...
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

"Rick Rothstein (MVP - VB)" wrote:

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