In this tutorial you will learn how to Find the Closest Value in Google Sheets.
To Find the Closest Value in Google Sheets you can use the following methods :
- Finding the closest Value using FILTER() function
- Finding the closest Value using QUERY() function
How to Find the Closest Value in Google Sheets
Enclosed are the steps to Find the Closest Value in Google Sheets :
#First Approach: Finding the closest Value using FILTER
=FILTER(A2:B12,ABS(D2-B2:B12)=min(ABS(D2-B2:B12)))
This approach identifies the row in the range A2:B12 whose value in the range B2:B12 is closest to the value in cell D2.
The following example shows how to use the above method in Google Sheets.
Example 1: Finding the closest Value using FILTER
We will use the below dataset to find the closest value
We have used the formula to calculate the closest value as shown below.
Tottenham Hotspur is the team with the closest points total of 29. They had a total of 29 points.
Approach 2: Finding the Closest Value (Greater Than)
=QUERY(A2:B12,"select A, B where B >= "&D2&" order by B limit 1",0)
This approach locates the row in the range A2:B12 where the value in column B is closest to and larger than or equal to the value in cell D2.
The following example shows how to use the above method in Google Sheets.
Manchester City has a points value that is closest to 45 while being equal to or greater than 45. They scored 46 points.
How do you find the highest and lowest value in Google Sheets?
A set of values’ minimum value is returned by the MIN function. You can either provide multiple values (e.g., =MIN(value1, value2, )) or a range of cells (e.g., =MIN(A1:A15)) whose minimum value you want to know. A set of values’ maximum value is returned by the MAX function.
How to Find the Closest Value in Google Sheets(Conclusion)
In the above tutorial, we have shown you two methods using which you can Find the Closest Value in Google Sheets.
We hope this tutorial was helpful.
Related posts :
How to Convert Numbers to Strings in Google Sheets(Quick & Easy Guide)
How to Calculate Average If Cell Contains Text in Google Sheets
How to Combine First and Last name in Google Sheets(Quick & Easy Guide)
How to Round to Significant Figures in Google Sheets(Quick & Easy Guide)
