View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Ron Rosenfeld[_2_] Ron Rosenfeld[_2_] is offline
external usenet poster
 
Posts: 1,045
Default Formula help, please.

On Fri, 29 Jun 2012 00:52:10 +0000, saroman wrote:


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.


Since you indicate the range of possible combinations, then you can try:

=INDEX({10,6,4,2,1},MATCH(A1,{"A","B","C"},0)+MATC H(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.