Lookup excel return min value if no match found

Question

If number from first coulmn is found in the second column, it should return that number. If number from first coulmn is not found in the secound column, it should return closest possible min value

Answer 1

You can use vlookup() like this, BUT you need to sort the values:

Answer 2

In C1 enter the array formula:

=MAX(IF(B:B<=A1,B:B))

and copy downward:

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

NOTE:
Sorting is not required.
Array entry is not required if you are using Excel 365.

Answer 3

Another option is SUMPRODUCT:

=SUMPRODUCT(MAX(--($B$1:$B$12<=A1)*$B$1:$B$12))

It works on Excel 2007 or higher, no need of array entered formula and no need of sorting.