This works just for your typed example.

=IF(AND(D1A1,D1<B1),40)

If you want to cover the 3 amounts you've listed, the try this.

=IF(AND($D$1$A2,$D$1<$B2),C4,IF(AND($D$1$A3,$D$1 <$B3),C3,IF(AND($D$1$A4,$D$1<B4),C2)))

Now your question actually states "between" the two numbers. If you want to
include the two numbers in your formula, then:

=IF(AND($D$1=$A2,$D$1<=$B2),$C2,IF(AND($D$1=$A3, $D$1<=$B3),C3,IF(AND($D$1=$A4,$D$1<=B4),C4)))

HTH,
Paul




"MIchel Khennafi" wrote in message
...
I have the following table

A B C
1 From To Amount
2 0 10 10
3 11 20 30
4 21 50 40

I would like to enter a value in D1 Cell -- 34
What is the formula that is going to return 40 because 34 is in between 21
and 50?

Thanks so much