series of conditional formulas

[nilizandr]

hello,
i would like to create a formula which says:

if the value in column A is -1, and the value in column B is 2, and th
value is column C is 0, then return the value -1
AND
if the value in column A is 2, and the value in column B is 1, and th
value is column C is -1, then return the value 0
AND
etc.etc.

There is a finite list of possible permutations for the values i
columns A, B, and C, and I would like to define a result for eac
possible permutation. I know that I could accomplish this b
dedicating a separate column and IF() formula to each possible outcome
then summing the results, but I'd love to do something more elegant i
just one column. Any thoughts?

Thanks!
Nil

 

[Frank Kabel]

Hi
one way:
1. On a separate table (e.g. called 'lookup') create a table with all
your combinations in columns A:C and the return value in column D

2. In your other sheet use the following array formula (entered with
CTRL+SHIFT+ENTER) in column D:
D1:
=INDEX('lookup'!$D$1:$D$20,MATCH(1,('lookup'!$A$1:$A$20=A1)*('lookup'!$
B$1:$B$20=B1)*('lookup'!$C$1:$C$20),0))
and copy this down

After this sum this column D