Retrieve cell value from a sorted range when an "input" cell value is >= than the first cell in the range AND < than the next cell in the range

Question

I'm trying to write a formula that retrieves a value from a sorted range when another cell value (the "input") is >= than the first cell in the range and < than the next value in this range (if there is a way to do it without sorting the range I'm happy with it too).

I tried using query but I can't find a way to make it work.

This is the sheet I have:

A (input)	B
910	0
	1000
	4000
	7000

and I would like to retrieve 0.

In this other case I would like to retrieve 4000:

A (input)	B
5440	0
	1000
	4000
	7000

I need this formula for a VLOOKUP search_key that I will use to retrieve another value from cell C =VLOOKUP(A2,B2:C5,2,FALSE), so instead of having to put the exact value I'm trying to vlookup in A2 I can get it by comparing A2 to the range.

Answer 1

=vlookup(A7,$A$1:$A$4,1,1)

or

=xlookup(A7,$A$1:$A$4,$A$1:$A$4,,-1)

Result:

Also you can sort directly in formula:

=vlookup(A7,sort($A$1:$A$4,1,1),1,1)