Is there a better way than this to code IF statements in a worksheet?


Code:
--------------------
=IF(H8=1, A1, IF(H8=2, A2, IF(H8=3, A3, A4)))
--------------------


As the code shows, the common reference cell is H8 and the IF statement
checks it's value (from a range of 1-3) and then makes the target cell
equal one out of a range of cells depending on the outcome.

This is a short example but I have some very long and complex IF
statements that use this method and was wondering if there was a way to
simplify e.g. by using array formulas or something similar?

Something along the lines of:


Code:
--------------------
=[IF(H8 = 1;2;3, A1;A2;A3, A4)]
--------------------


That obviously doesn't work but that's the kind of thing I was
thinking.

Cheers
-Rob


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=387970

One way:

Since you indicate that H8 will always be 1, 2, or 3:

=CHOOSE(H8, A1, A2, A3)

but then you throw in A4, so perhaps

=IF(AND(H8=1,H8<=3),CHOOSE(H8,A1,A2,A3),A4)

or, depending on what values H8 may take on, a Lookup formula would be
better.



In article ,
TheRobsterUK
wrote:

Is there a better way than this to code IF statements in a worksheet?


Code:
--------------------
=IF(H8=1, A1, IF(H8=2, A2, IF(H8=3, A3, A4)))
--------------------


As the code shows, the common reference cell is H8 and the IF statement
checks it's value (from a range of 1-3) and then makes the target cell
equal one out of a range of cells depending on the outcome.

This is a short example but I have some very long and complex IF
statements that use this method and was wondering if there was a way to
simplify e.g. by using array formulas or something similar?

Something along the lines of:


Code:
--------------------
=[IF(H8 = 1;2;3, A1;A2;A3, A4)]
--------------------


That obviously doesn't work but that's the kind of thing I was
thinking.

Cheers
-Rob

If cell H8 will actually contain integer values of 1 or greater, you
might be able to work with one of these:

=CHOOSE(H8, A1,A2,A3,A4,A5)
whe
If H8 is 2, the formula returns A2
If H8 is 5, the formula returns A5

OR

=INDIRECT("A"&H8)

Does that help?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=387970

I'd use VLOOKUP. Might be cleaner at the end.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=387970

If your cells to be polled are in a specific, contiguous range, you could
try this:

=INDEX(A1:A4,H8)

Where there is *no* A4 "fallback" return, but an A4 specific reference.

If the A4 return is necessary, you could try something like this:

=IF(ISERR(INDEX(A1:A3,H8)),A4,INDEX(A1:A3,H8))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"TheRobsterUK"
wrote in message
news:TheRobsterUK.1sd9eb_1121691903.8943@excelforu m-nospam.com...

Is there a better way than this to code IF statements in a worksheet?


Code:
--------------------
=IF(H8=1, A1, IF(H8=2, A2, IF(H8=3, A3, A4)))
--------------------


As the code shows, the common reference cell is H8 and the IF statement
checks it's value (from a range of 1-3) and then makes the target cell
equal one out of a range of cells depending on the outcome.

This is a short example but I have some very long and complex IF
statements that use this method and was wondering if there was a way to
simplify e.g. by using array formulas or something similar?

Something along the lines of:


Code:
--------------------
=[IF(H8 = 1;2;3, A1;A2;A3, A4)]
--------------------


That obviously doesn't work but that's the kind of thing I was
thinking.

Cheers
-Rob


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile:
http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=387970