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

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.

CodePudding user response：

Try this, Or take a look at the

CodePudding user response：

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)))