Alternative for Nested IF formula

[Luke_1814]

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

<tbody>
</tbody>

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.

 

[Special-K99]

Use a lookup table.

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

e.g.

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

 

[Aladin Akyurek]

Looks like...

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

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

 

[mart37]

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

 

[Luke_1814]

=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

 

[Luke_1814]

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

Thank you for your response

 

[Luke_1814]

=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.
Please help.


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

 

[Aladin Akyurek]

How about post #3 ?

 

[mart37]

depends how many decimals. 0.4999999999 have a beter result.

 

[Luke_1814]

How about post #3 ?

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.