# Excel - Formula help, please.

Asked By saroman on 28-Jun-12 08:52 PM
```I have a table in which the entries in column A can be A, B or C and the
entries in column B can be D, E or F (A through F are text entries, not
numbers).  I want column C to return numbers representing the
combination of the text entries in columns A and B.  For example, if
cell A1 is A and cell B1 is D, I want cell C1 to return the number 10.
Similarly, if cell A2 is B and cell B2 is E, I want cell C2 to return
the number 4.

These are the possible combinations:
Column A/Column B/Column C
A/D/10
A/E/6
A/F/4
B/D/6
B/E/4
B/F/2
C/D/4
C/E/2
C/F/1

How do I do this?  Thanks.

--
saroman```

Auric__ replied to saroman on 29-Jun-12 05:03 AM
```Paste this into C1 and then copy down (watch the wordwrap; this is all one
line):

=IF(A1="A",IF(B1="D",10,IF(B1="E",6,IF(B1="F",4,""))),IF(A1="B",IF(B1
="D",6,IF(B1="E",4,IF(B1="F",2,""))),IF(A1="C",IF(B1="D",4,IF(B1="E",2,IF(B1
="F",1,""))),"")))

There is probably a better way to do it, but this works.

--
He carried himself like an angry ox.```
Ron Rosenfeld replied to saroman on 29-Jun-12 06:24 AM
```Since you indicate the range of possible combinations, then you can try:

=INDEX({10,6,4,2,1},MATCH(A1,{"A","B","C"},0)+MATCH(B1,{"D","E","F"},0)-1)

Any other combination will result in an error message.
The match is case INsensitive. If you need a case SENSITIVE match, post back.```
saroman replied to Ron Rosenfeld on 29-Jun-12 12:07 PM
```Thanks so much.  Your solution worked.  The only difference between your
solution and the first one suggested is that, as you indicated, the
other combinations returned "#N/A" to the cells in column C whereas the
first solution left those cells blank.

'Ron Rosenfeld[_2_ Wrote:

--
saroman```
Ron Rosenfeld replied to saroman on 29-Jun-12 10:42 PM
`Glad to help.  Thanks for the feedback.`