Formula to round up to the nearest value in a table

[UncleBajubjubs]

Hello, I have a table which shows the engine size necessary for specific horsepowers.
Example:
5 hp Size 1
10 hp Size 2
20 hp Size 3
50 hp Size 4

I want to have a cell which displays the proper engine size needed, the issue is that horsepowers can often be values in between the listed sizes, and thus aren't on the table, so the horsepower would need to be rounded up. So a 6 hp will require Size 2, 25 hp will require size 4, etc. I'm not sure how to do this properly, as simply rounding won't get me values on the table, and it always has to round up to a value on the table. Thanks.

 

[Eric W]

Try:

	A	B	C	D	E	F	G
hp	Size needed	Table
Size 1	Size 1
Size 2	Size 2
Size 2	Size 3
Size 4	Size 4

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5.1[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"]10.1[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"]20.1[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"][/TD]

</tbody>

<p s

</p>[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=VLOOKUP(A2,$D$2:$F$5,3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


You can also build the table in the formula like this:

=LOOKUP(A2,{0,5.1,10.1,20.1},{"Size 1","Size 2","Size 3","Size 4"})

but it's usually easier to leave the table on the sheet somewhere so you can change it if need be.

 

[Scott T]

Avoids the helper column but must be sorted Highest to lowest.

Book1
	A	B
1	HP	Size need
2	50	Size4
3	20	Size3
4	10	Size2
5	5	Size1
6
7
8
9	1	Size1
10	25	Size4
Sheet1

Cell Formulas
Range	Formula
B9	=INDEX($B$2:$B$5,MATCH(A9,$A$2:$A$5,-1))
B10	=INDEX($B$2:$B$5,MATCH(A10,$A$2:$A$5,-1))