# Thread: Alternative for Nested IF formula

1. ## Alternative for Nested IF formula

Hi Guys,
Chose the right box to store Part A which has volume of 2.7

 Box name(Column A) Box Volume(Column B) b1 1 b2 2 b3 3

Based on on above table i would use an if conditions which goes like this,
=IF(\$F\$1<=B2,A2,IF(\$F\$1<=B3,A3,IF(\$F\$1<=B4,A4)))

is there any other alternative instead of using IF function. the above example contains 3 rows, however in reality i would need to apply IF func for 100+ rows. Please help.

2. ## Re: Alternative for Nested IF formula

Use a lookup table.

VLOOKUP or INDEX(...MATCH...)

e.g.

=VLOOKUP(Lookupvalue,Sheet2!A\$2:B\$4,2,0)

3. ## Re: Alternative for Nested IF formula

Looks like...

=LOOKUP(F1,B:B,A:A)

for A:B seems to be sorted inascending order on column B.

4. ## Re: Alternative for Nested IF formula

=IFERROR(INDEX(A2:A4,(MATCH(ROUND(F1+0.4,0),B2:B4,1))),"Don't fit!")

5. ## Re: Alternative for Nested IF formula

Originally Posted by mart37
=IFERROR(INDEX(A2:A4,(MATCH(ROUND(F1+0.4,0),B2:B4,1))),"Don't fit!")
Thank you for the above response. Worked well! Appreciate it

6. ## Re: Alternative for Nested IF formula

It worked only incase if the value is an integer. however in case there is decimal it takes the 1st unit place value.

7. ## Re: Alternative for Nested IF formula

Originally Posted by mart37
=IFERROR(INDEX(A2:A4,(MATCH(ROUND(F1+0.4,0),B2:B4,1))),"Don't fit!")
Hi,
Ideally if i use F1 value more than 3 it should show me "Don't fit", however in this case the result shows a A4 Cell.

And may i know why have you used F1+0.4?

9. ## Re: Alternative for Nested IF formula

depends how many decimals. 0.4999999999 have a beter result.

10. ## Re: Alternative for Nested IF formula

Hi,
it didn't work if the number is decimal. based on the above example the part should go to B3 however usung the formula you mentioned, it chose B2.