Formula for Greater/Less Than

[DanPV]

It seems I have a simple problem however I have tried an If Statement, Vlookup, and an Index formula to no avail. What I am trying to accomplish is retrieve data from a table that has three columns - all numeric. The formula I want is to say if cell A1 is greater than column one and less than column two, return column three. The IF statement below does not work:
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD id=td_post_1505372 style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 48pt; BORDER-BOTTOM: #ffffff; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>=IF(A4>=$A$16,D16,IF(A4<=$B$16,D16,IF(A4>=$A$17,D17,IF(A4<=$B$17,D17,IF(A4>=$A$18,D18,IF(A4<=$B$18,D18))))))</TD></TR></TBODY></TABLE>
Thank you,
-D

 

[texasalynn]

Welcome to MrExcel board....

try something like this

=IF(AND(A4> =$A$16,A4< =$B$16),D16,D18)

 

[JackDanIce]

Does this work?

=IF(AND(A4>=A16, A4<=B16),D16,IF(AND(A4>=A17, A4<=B17),D17,D18))

You haven't given the case when A4 doesn't fit any of the categories (i.e. it's not inbetween A16 and B16 or A17 and B17 or A18 and B18)

 

[DanPV]

The result is not consistent - see below:

Table:

800 1000 45000
1100 1500 50000
1600 2000 60000

Result:

895 45000
1200 50000
1700 60000
900 0

Or if I absolute the cell it returns 45000,45000,45000,60000

Thanks
-D

 

[Aladin Akyurek]

Looks like...

Control+shift+enter, not just enter:

=INDEX($D$16:$D$18,MATCH(1,IF($A$16:$A$18>=$A4,IF($A4<=$B$16:$B$18,1)),0))

 

[JackDanIce]

My formula returns the same values as the first three you have posted but for 900 returns 45000, i.e.:

895 - 45000
1200 - 50000
1700 - 60000
900 - 45000

 

[DanPV]

I added another If/And at the end to satisfy the last row and it worked. I appreciate the help.