Finding a max value

[smbutler]

Hi,
I have 2 columns of data, one with distances and one with pixel values. Then, i have two columns that also have distances in them, forming a range. I would like to have a formula that looks at the range of values and then looks up those same values in the first column of distances and picks out the max pixel value from that. I tried Dmax, but it seems to only work with the first row because its directly below the criteria labels.

So, for example, I would like to look up the maximum pixel value within the range of distances listed (>.003 and <.023, for example). The columns look like this (with the distance and pixel column being longer, encompassing values throughout the ranges):
Any ideas?

Distance Pixel Distance Distance
0 168 >0.003 <0.023
0.00 176 >0.023 <0.043
0.00 178 >0.053 <0.073
0.001 179 >0.07 <0.09
0.002 155 >0.09 <0.11
0.002 160 >0.122 <0.142
0.002 170
0.003 159
0.003 165
0.004 153
0.004 159
0.005 156
0.005 162
0.005 169
0.006 152
0.006 158
0.007 145
0.007 145
0.007 147
0.008 130
0.008 142
0.009 133
0.009 152
0.01 137
0.01 146
0.01 157
0.011 145
0.011 154
0.012 141
0.012 149
0.013 153
0.013 161.52

[NBVC]

Assuming your data in in columns A1:D100 and all data is numerical, then in E2,

=MAX(IF($A$2:$A$100>C2,IF($A$2:$A$100<D2,$B$2:$B$100)))

adjust ranges to suit.

confirmed with CTRL+SHIFT+ENTER not just ENTER...you'll see {} brackets appear around the formula, then copy down.

[smbutler]

That makes sense, but it doesn't seem to be working. I get 0 back instead of the max pixel value. Is there something I am doing wrong?
thanks for your help

[NBVC]

Do you actually have the > and < symbols in the Distance cells in the last 2 columns? My formula assumes you don't (it is better to have just the numerical...the formula takes care of the less than or greater than).

Then remember that you must confirm the formula with CTRL+SHIFT+ENTER not only ENTER/

[smbutler]

Thanks! that worked!