Formula to return specific value between 2 numbers

[rplohocky]

Hello,
After much research I haven't been able to find the formula I need.

I am looking for a formula that will return a value based on what was entered into A2. I would like to insert a value into A2 then have the formula return a specific value base on a range. The range would be: If A2 is greater than or equal to 1 but not greater than 85, then return 100. If A2 is equal to or greater than 86 but not greater than 185, then return 200. If A2 is equal to or greater than 186 but not greater than 285, then return 300. If A2 is equal to or greater than 286 but not greater than 385, than return 400. I know how to do this using nested IF statements but I exceed the 7 IF limit by a few which is why I was trying to use IFS(AND( but No luck.

This is where I started but always gets hung up.
=IFS(AND([@[Drop Length]]>1,[@[Drop Length]]<=85),100,([@[Drop Length]]>=86,[@[Drop Length]]<=185),200,"")

 

[etaf]

you could use a lookup table
=INDEX(D2:D7,MATCH(A2,C2:C7,1))

where you have a table in C & D
<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>[TABLE="width: 174"]
<!--StartFragment--> <colgroup><col width="87" span="2" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 87, align: right"]0[/TD]
[TD="width: 87"][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]85[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]185[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD="align: right"]285[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]385[/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[DanteAmor]

Could be

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">780</td><td style="text-align:right; ">800</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formula</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=IF(A2<86,100,IF(A2<186,200,IF(A2<286,300,IF(A2<386,400,IF(A2<486,500,IF(A2<586,600,IF(A2<686,700,IF(A2<786,800))))))))</td></tr></table></td></tr></table> <br /><br />

Or

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">280</td><td style="text-align:right; ">300</td><td style="text-align:right; ">0</td><td style="text-align:right; ">100</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td style="text-align:right; ">86</td><td style="text-align:right; ">200</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:right; ">186</td><td style="text-align:right; ">300</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="text-align:right; ">286</td><td style="text-align:right; ">400</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="text-align:right; ">386</td><td style="text-align:right; ">500</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td style="text-align:right; ">486</td><td style="text-align:right; ">600</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td style="text-align:right; ">586</td><td style="text-align:right; ">700</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td style="text-align:right; ">686</td><td style="text-align:right; ">800</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td style="text-align:right; ">786</td><td style="text-align:right; ">900</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td style="text-align:right; ">886</td><td style="text-align:right; ">800</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=VLOOKUP(A2,C3:D11,2,1)</td></tr></table></td></tr></table> <br /><br />

 

[Tetra201]

This looks like a classic case for the LOOKUP:

=LOOKUP(A2,{-9.99E+99,1,86,186,286,386},{"Out of range",100,200,300,400,"Out of range"})

 

[rplohocky]

This looks like a classic case for the LOOKUP:

=LOOKUP(A2,{-9.99E+99,1,86,186,286,386},{"Out of range",100,200,300,400,"Out of range"})

Thank you Tetra201! This worked perfectly! Thank you to everyone else as well!

 

[DanteAmor]

You're welcome