Table of Contents
Finding the closest value in Google Sheets can be achieved through the use of the VLOOKUP function. This function allows users to search for a specific value within a given range and return the closest matched value. By specifying the lookup value and the range to search within, the VLOOKUP function will return the closest value based on the defined criteria. This feature is useful for finding approximate matches or for identifying the nearest value in a large data set. Using the VLOOKUP function in Google Sheets can help users quickly and accurately locate the closest value, saving time and effort in data analysis.
Find the Closest Value in Google Sheets (With Examples)
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
Cite this article
stats writer (2024). How can I find the closest value in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-find-the-closest-value-in-google-sheets/
stats writer. "How can I find the closest value in Google Sheets?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-find-the-closest-value-in-google-sheets/.
stats writer. "How can I find the closest value in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-find-the-closest-value-in-google-sheets/.
stats writer (2024) 'How can I find the closest value in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-find-the-closest-value-in-google-sheets/.
[1] stats writer, "How can I find the closest value in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I find the closest value in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.