Formula to get the data compared to table

[Sathisc]

Hi,

I have the below table with "From (column A), To(Column B) & Unit(Column C)". In E1 when the data is entered, the formula should check if the numbers is between or equal to the number and give the updated units. In the below table if E1 = 200 then E2 should get 3.5(formula) comparing the table.


[TABLE="width: 144"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]From[/TD]
[TD="width: 64, bgcolor: transparent"]To[/TD]
[TD="width: 64, bgcolor: transparent"]Units[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]45[/TD]
[TD="bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]46[/TD]
[TD="bgcolor: transparent"]60[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]61[/TD]
[TD="bgcolor: transparent"]75[/TD]
[TD="bgcolor: transparent"]1.25[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]76[/TD]
[TD="bgcolor: transparent"]90[/TD]
[TD="bgcolor: transparent"]1.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]91[/TD]
[TD="bgcolor: transparent"]105[/TD]
[TD="bgcolor: transparent"]1.75[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]106[/TD]
[TD="bgcolor: transparent"]120[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]121[/TD]
[TD="bgcolor: transparent"]135[/TD]
[TD="bgcolor: transparent"]2.25[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]136[/TD]
[TD="bgcolor: transparent"]150[/TD]
[TD="bgcolor: transparent"]2.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]151[/TD]
[TD="bgcolor: transparent"]165[/TD]
[TD="bgcolor: transparent"]2.75[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]166[/TD]
[TD="bgcolor: transparent"]180[/TD]
[TD="bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]181[/TD]
[TD="bgcolor: transparent"]195[/TD]
[TD="bgcolor: transparent"]3.25[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]196[/TD]
[TD="bgcolor: transparent"]210[/TD]
[TD="bgcolor: transparent"]3.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]211[/TD]
[TD="bgcolor: transparent"]225[/TD]
[TD="bgcolor: transparent"]3.75[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]226[/TD]
[TD="bgcolor: transparent"]240[/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]241[/TD]
[TD="bgcolor: transparent"]255[/TD]
[TD="bgcolor: transparent"]4.25[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]256[/TD]
[TD="bgcolor: transparent"]270[/TD]
[TD="bgcolor: transparent"]4.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]271[/TD]
[TD="bgcolor: transparent"]285[/TD]
[TD="bgcolor: transparent"]4.75[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]286[/TD]
[TD="bgcolor: transparent"]300[/TD]
[TD="bgcolor: transparent"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]301[/TD]
[TD="bgcolor: transparent"]315[/TD]
[TD="bgcolor: transparent"]5.25[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]316[/TD]
[TD="bgcolor: transparent"]330[/TD]
[TD="bgcolor: transparent"]5.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]331[/TD]
[TD="bgcolor: transparent"]345[/TD]
[TD="bgcolor: transparent"]5.75[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]346[/TD]
[TD="bgcolor: transparent"]360[/TD]
[TD="bgcolor: transparent"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]361[/TD]
[TD="bgcolor: transparent"]375[/TD]
[TD="bgcolor: transparent"]6.25[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]376[/TD]
[TD="bgcolor: transparent"]390[/TD]
[TD="bgcolor: transparent"]6.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]391[/TD]
[TD="bgcolor: transparent"]405[/TD]
[TD="bgcolor: transparent"]6.75[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]406[/TD]
[TD="bgcolor: transparent"]420[/TD]
[TD="bgcolor: transparent"]7[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]421[/TD]
[TD="bgcolor: transparent"]435[/TD]
[TD="bgcolor: transparent"]7.25[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]436[/TD]
[TD="bgcolor: transparent"]450[/TD]
[TD="bgcolor: transparent"]7.5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]451[/TD]
[TD="bgcolor: transparent"]465[/TD]
[TD="bgcolor: transparent"]7.75[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]466[/TD]
[TD="bgcolor: transparent"]480[/TD]
[TD="bgcolor: transparent"]8[/TD]
[/TR]
</tbody>[/TABLE]

thanks in advance

 

[Joe4]

Simply use VLOOKUP with the approximate match argument.
Set you lookup tables exactly as you have shown here.

Let's say that the data is in cells A2:C31, and the value you are looking up is in cell E1.
Then use this formula:

=VLOOKUP(E1,A2:C31,3,TRUE)

You can Google "Excel VLOOKUP" if you want to learn more about VLOOKUP, and see other examples.

 

[Sathisc]

it works. Thank you so much Joe

 

[Joe4]

You are welcome.