You can use the following methods to find the closest value to some number in Google Sheets:

**Method 1: Find Closest Value**

=FILTER(A2:B15,ABS(D2-B2:B15)=min(ABS(D2-B2:B15)))

This method finds the row in the range **A2:B15** where the value in the range **B2:B15** is closest to the value in cell **D2**.

**Method 2: Find Closest Value (Greater Than)**

=QUERY(A2:B15,"select A, B where B >= "&D2&" order by B limit 1",0)

This method finds the row in the range **A2:B15** where the value in column **B** is closest to the value in cell **D2** and is also greater than or equal to the value in cell **D2**.

The following examples show how to use each method with the following dataset in Google Sheets:

**Example 1: Find Closest Value**

We can use the following formula to find the row in **A2:B15** where the “points” value is closest to 31:

=FILTER(A2:B15,ABS(D2-B2:B15)=min(ABS(D2-B2:B15)))

The following screenshot shows how to use this formula in practice:

The team with the points value closest to **31** is the Hornets. They had **30** points.

**Example 2: Find Closest Value (Greater Than)**

We can use the following formula to find the row in **A2:B15** where the “points” value is closest to 31 and is also greater than or equal to 31:

=QUERY(A2:B15,"select A, B where B >= "&D2&" order by B limit 1",0)

The following screenshot shows how to use this formula in practice:

The team with the points value closest to **31** while being equal to or greater than **31** is the Knicks. They had **36** points.

**Additional Resources**

The following tutorials explain how to perform other common operations in Google Sheets:

Google Sheets: How to Use COUNTIF From Another Sheet

Google Sheets: How to Use COUNTIF with Multiple Ranges

Google Sheets: How to Use COUNTIFS with a Date Range

Hi would love to use the second option but witha. smaller than, though it does not seem to work in the same way, it always provides me with the minimum. Anywar you could help me with thisº

thanks! its very helpful

The query function seems to be exactly what I need, and when I input:

=QUERY(B:B, “select B where B >= “&I2&” limit 1″, 0)

it works perfectly for finding the nearest “greater than” values. However, I also need to find the nearest “less than” values, but when I input the identical function with a less than operator:

=QUERY(B:B, “select B where B <= "&I2&" limit 1", 0)

it only returns a blank cell. I can't figure out why this would be the case. Do you have a solution for this?