Return higher value within range of Cells

[ElectroBins]

I have the following table sorted by Column A, X = my input value. I want value Y =corresponding value in B to:
- Return Y value in column B (rounded up to the next row value).
- If value is less than the minimum return the minimum
- If value is more than the max return an error message.

i.e:
X <=20, Y=44
X= 276, Y=24
X=275. Y=6
X>=1000 Y=Error message
The issue comes when X value is outside the table range, I can solve one but not the other and by adding +1 to match function it goes out of range from one end and inside.

Note: I want to avoid using vlookup.

[TABLE="******* 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]

B​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]43[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]230[/TD]
[TD]33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]261[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]275[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]502[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Just when you think you have many many things sorted it is funny how the simplest can hold you down.

 

[sheetspread]

Note: I want to avoid using vlookup.

But lookup is the best solution, any special reason you don't want it?

 

[63falcondude]

What if X = 21?

 

[ElectroBins]

vlookup can only go one way in a table. I also have a monster of a file and if I add a column to my table vlookup does not index correctly. I converted all my formulas to Index and match and it is working like a charm.

 

[sheetspread]

Vlookup with choose and regular lookup can search leftwards. Both require your large data set to be rearranged, however, which may not be practical.