IF Function too Large
 

The function below is a partial version of the correct one; I will exceed the
allowed characters for the field (too many arguments). I notice I am using
different arguments which could be combined as a range: C16=5:7 for the same
results, in other words 5, 6 & 7 all = E20 as a result.

=IF(C16=5,E20,IF(C16=6,E20,IF(C16=7,E20,IF(C16=8,E 21,IF(C16=9,E21,IF(C16=10,E22,IF(C16=11,E22,IF(C16 11,E23,0))))))))

How can I build this function combining criteria as a range for a common
result?

--
Thank you,

Trying Hard

IF Function too Large
 

Here are a couple things you might try:

If there won't be a huge number of options AND the pattern of referenced
cells may vary:
=CHOOSE(INT((C16-2)/3),C20,C21,C22,C23,C24,C25,C26,C27,C28)
(you can add other cell references to that list)

Or, the reference pattern is consistent:
=INDEX(C:C,20+INT((C16-5)/3))


Does that give you something to work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Trying Hard" wrote:

The function below is a partial version of the correct one; I will exceed the
allowed characters for the field (too many arguments). I notice I am using
different arguments which could be combined as a range: C16=5:7 for the same
results, in other words 5, 6 & 7 all = E20 as a result.

=IF(C16=5,E20,IF(C16=6,E20,IF(C16=7,E20,IF(C16=8,E 21,IF(C16=9,E21,IF(C16=10,E22,IF(C16=11,E22,IF(C16 11,E23,0))))))))

How can I build this function combining criteria as a range for a common
result?

--
Thank you,

Trying Hard

IF Function too Large
 

Ron,

I messed up and posted twice, I ended up with:

=INDEX(E19:E23,MATCH(C16,{0,5,7,10,15}))


--
Thank you,

Trying Hard


"Ron Coderre" wrote:

Here are a couple things you might try:

If there won't be a huge number of options AND the pattern of referenced
cells may vary:
=CHOOSE(INT((C16-2)/3),C20,C21,C22,C23,C24,C25,C26,C27,C28)
(you can add other cell references to that list)

Or, the reference pattern is consistent:
=INDEX(C:C,20+INT((C16-5)/3))


Does that give you something to work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Trying Hard" wrote:

The function below is a partial version of the correct one; I will exceed the
allowed characters for the field (too many arguments). I notice I am using
different arguments which could be combined as a range: C16=5:7 for the same
results, in other words 5, 6 & 7 all = E20 as a result.

=IF(C16=5,E20,IF(C16=6,E20,IF(C16=7,E20,IF(C16=8,E 21,IF(C16=9,E21,IF(C16=10,E22,IF(C16=11,E22,IF(C16 11,E23,0))))))))

How can I build this function combining criteria as a range for a common
result?

--
Thank you,

Trying Hard