Slight different case of multiple condition with regard to if

[paradise2sr]

I tried to develop a formula that has the following condition,

If K1 = 1 then result must be ""
If K1 = 2 then result must be 6
If K1 = 3 then result must be 43
If K1 = 4 then result must be J4+6
If K1 = 5 then result must be J4+6
If K1 = 6 then result must be J4+6
If K1 = 7 then result must be J4+6

Can anyone help in this regard

[elayaz]

Try this..

=IF(K1=1,"",(IF(K1=2,6,(IF(K1=3,43,(IF(K1=4,J4+6,(IF(K1=5,J4+6,(IF(K1=6,J4+6,IF(K1=7,J4+6,"NA"))))))))))))

[zbor]

Here:

=CHOOSE(MIN(4,K1),"",6,43,J4+6)

[paradise2sr]

Thanx Zbor,

Can you elaborate your formula in details.Your formula is so short and very sweet.Also how would you revise your formula in case,

If K1 = 1 then result must be ""
If K1 = 2 then result must be 38
If K1 = 3 then result must be 43
If K1 = 4 then result must be ""
If K1 = 5 then result must be ""
If K1 = 6 then result must be ""
If K1 = 7 then result must be ""

I have used accordingly as

=CHOOSE(MIN(4,K1),"",38,43,"")

Kindly explain your formula as well as below formula is correct or not,that I have made similarly like yours,if any mistake kindly modify accordingly .

I marking this thread as solved.Thanx to "Elayaz" too.

With Best Rgds

[zbor]

You wrote correct.

CHOOSE function return n-th value defined by index number.

CHOOSE(index_num,value1,value2,value3...)

If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.

In your case you need to return:,"",38,43,"" wehre index_num is defined by value in K1 (1,2,3 or 4)
If K1 is bigger than 4 you will get error (because 5th value in CHOOSE function is not defined).
But since they are all same after 4th value you can use always that value (4) so you need

MIN(4,K1)

That will return 1,2,3 or 4

MIN(4,1)	1
MIN(4,2)	2
MIN(4,3)	3
MIN(4,4)	4
MIN(4,5)	4
MIN(4,6)	4

If this not the case, if values after 4 are different, then you need to define all of them in CHOOSE function.
That would make CHOOSE function too huge and I would rather go with VLOOKUP function in that case (but that's another sotry).

At the end, let's sumarize once again:
Index_num 3 will return 3rd value in your choose function that is 43

=CHOOSE(index_num,value1,value2,value3...)

=CHOOSE(3,"",38,43,"")

[Ace_XL]

Another way..
=IF(K1>=4,J4+6,INDEX({"",6,43},K1))

answer to your second question would be
=INDEX({"",38,43,""},MIN(K1,4))

[paradise2sr]

Thanks all of the forum contributors.Indeed,this had minimised my work.