Google sheets: Compare a number with a range of numbers and pick the closest lower number

Question

I have a random number in range 300-450, like 418. Then, I have 5 cells with numbers 300, 330, 360, 390, 420.

I need to find out the nearest lower number of 418. So, It should refer to the cell with number 390.

Is there an easy way? Thank you.

Answer 1

if 390 is your desired result use:

=VLOOKUP(C2, A1:A5, 1, 1)

if your 5 cells are in row you can use HLOOKUP

---

otherwise, the closest is 420:

=FILTER(A1:A5, ABS(C2-A1:A5)=MIN(ABS(C2-A1:A5)))