referencing a cell

  • Thread starter MIchel Khennafi
  • Start date
M

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
 
P

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
 
P

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.
 
Top