referencing a cell

[MIchel Khennafi]

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

 

[PCLIVE]

This works just for your typed example.

=IF(AND(D1>A1,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

 

[PCLIVE]

This works just for your typed example.

=IF(AND(D1>A1,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

 

[Guest]

Try something like this:

Using your example data in A1:C4
E1: =VLOOKUP(D1,A2:C4,3,1)

See Debra Dalgleish's website for information on the VLOOKUP function:
http://www.contextures.com/xlFunctions02.html

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Try something like this:

Using your example data in A1:C4
E1: =VLOOKUP(D1,A2:C4,3,1)

See Debra Dalgleish's website for information on the VLOOKUP function:
http://www.contextures.com/xlFunctions02.html

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[PCLIVE]

Ron,

That appears to work unless the lookup number is over 50. They didn't
really state what they wanted to happen in that situation, but the result
continues to be 40.
I'm sure an If statement could be added to avoid that. I like your solution
better than my though.

 

[PCLIVE]

Ron,

That appears to work unless the lookup number is over 50. They didn't
really state what they wanted to happen in that situation, but the result
continues to be 40.
I'm sure an If statement could be added to avoid that. I like your solution
better than my though.