IF Scenario

[will.00]

I want to return the output column (E) dependent on a combination of
sceanrios.

For example, if

Column A=B (possible results A or B)
Column B=2 ( possible results 1, 2 or 3)
Column C=Y (possible results Y or N)
Column D=1 (possible results 1 or -1)

The output will return 3.


A B C D
E

A 1 Y 1 1
A 1 N -1 2
B 2 Y 1 3
B 3 Y 1 4
B 1 N -1 5
B 2 N 1 6

Can I use a function that looks at the table in an array instead of a
million IF / AND formula.

Thanks in advance

 

[Niek Otten]

In F1, enter
=A1&B1&C1&D1
Fill down

If the values you look for are in a9:f9, then this is your formula:
=INDEX(E1:E6,MATCH(A9&B9&C9&D9,F1:F6,0))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel