Fill cells automatically from 2 variables

[Ned Harrison]

Hello - I am trying to add a function to a spreadsheet that means when 2
variables are completed (currency & month - 4 options on each) it returns a
predetermined value. I thought I'd nearly got there (with a large amount of
help) by using a combination of If & AND functions, but it keeps giving an
error message. Can anyone help?

In case, I'm not being clear, I need a formula that will deal with this:
If M =1, C=1, then E = a
If M =1, C=2, then E = b
If M =1, C=3, then E = c
If M =1, C=4, then E = d
If M =2, C=1, then E = e
If M =2, C=2, then E = f
If M =2, C=3, then E = g
If M =2, C=4, then E = h
If M =3, C=1, then E = i
If M =3, C=2, then E = j
If M =3, C=3, then E = k
If M =3, C=4, then E = l
If M =4, C=1, then E = m
If M =4, C=2, then E = n
If M =4, C=3, then E = o
If M =4, C=4, then E = p

Where M and C are selected by the user from 4 options, and E is the result
given from values that I have provided. Any ideas?
Many thanks
N

 

[Shane Devenshire]

Hi,

Set up a table like this

1 2 3 4
1 a e i m
2 b f j n
3 c g k o
4 d h l p

then us a formula like this
=INDEX($B$2:$E$5,MATCH(A11,$A$2:$A$5,),MATCH(A12,$B$1:$E$1,))

In this case the user is picking the M value from A11 and C value from A12
and the above table is in A1:E5

 

[Pete_UK]

So, are you saying that in your example "a", "b", "c" etc represent
values that you want to return rather than those letters literally?

One way of doing it is to put those values in a column somewhere, eg
in X1:X16, in the order shown, then you could use this in the cell you
refer to as E:

=INDEX(X$1:X$16,(M-1)*4+C)

where M and C will have to be changed to the cell references that they
represent.

You might like to build in some error-checking:

=IF(OR(M="",C=""),"",INDEX(X$1:X$16,(M-1)*4+C))

Hope this helps.

Pete

 

[Ned Harrison]

That is absolutely brilliant - Thank you!

Hi,

Set up a table like this

1 2 3 4
1 a e i m
2 b f j n
3 c g k o
4 d h l p

then us a formula like this
=INDEX($B$2:$E$5,MATCH(A11,$A$2:$A$5,),MATCH(A12,$B$1:$E$1,))

In this case the user is picking the M value from A11 and C value from A12
and the above table is in A1:E5

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[JP Ronse]

Hi Ned,

The usual way will require a lot of if's and goes perhaps beyond the limits
of Excel (I didn't try)...

I tried following and was working for me (if did understand your question
correctly...)

In column A, I've put M (1,1, 1, 1, 2, 2, 2, ...)
In column B, I've put C (1, 2, 3, 4, 1, 2, 3? 4 ...

In column C, (E result) following formula:

=IF(A1=1;CHAR(96+B1);IF(A1=2;CHAR(100+B1);IF(A1=3;CHAR(104+B1);CHAR(108+B1))))

The CHAR function gives the corresponding character of the ASCII value: 97
=> a, 98 => b, ....

Note that you may have to change ';' in ',' to get the formula working.

Please let me know if this was helpful.

Wkr,

JP

 

[Shane Devenshire]

Thanks for the feedback.

 

[Teethless mama]

Try this:

=CHAR(4*M1+C1+92)

 

[JP Ronse]

Hi Teetless mama,

That's what I would have answered if I was able to set-up the formula. Was
busy with M1^2 and didn't see 4*M1.

Wkr,

JP

 

[Ned Harrison]

Thanks people for all the responses. I've used Shane's version which seems
to work fine for what I need - but I really appreciate all the help. All of
your solutions look a lot simpler than my starting point. Cheers!