Fill cells automatically from 2 variables

  • Thread starter Thread starter Ned Harrison
  • Start date Start date
N

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
 
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
 
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
 
That is absolutely brilliant - Thank you!

Shane Devenshire said:
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
Click to expand...
 
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
 
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
 
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!
 
Back
Top