Have you ever needed to find the closest match in an array of numeric values in Google Sheets? Look no further! In this article, I’ll show you a simple method to accomplish this using the Filter, Min, and Abs functions combo.
Formulas to Find the Closest Match in Google Sheets
Let’s dive right into the formulas you’ll need to find the closest match in Google Sheets. First, here’s a sample dataset to work with: B1:B (where B1 contains the column name). The value you want to evaluate in the sample data is in cell D2.
Formula #1 (Filter [Google Sheets] Approach):
=filter(B2:B,ABS(D2-B2:B)=min(ABS(D2-B2:B)))Formula #2 (Index-Match [Excel] Approach):
=index(B2:B10,match(min(ABS(D2-B2:B10)),ABS(D2-B2:B10),0))Using these formulas, you can find the closest match to the value in cell D2 in the array B2:B. For example, if you change the value in D2 to 500, you can use both formulas to find the closest value.
The first formula will return two values, 400 and 600. However, the second formula will only return one value, 400.
Understanding the Formulas
Formula 1 – Filter Approach
The first formula is essentially a FILTER formula. It filters the range B2:B based on the condition ABS(D2-B2:B)=min(ABS(D2-B2:B)).
To break it down, let’s look at the two parts of the condition:
- Part 1:
ABS(D2-B2:B)returns the absolute difference between the numbers in B2:B and D2. - Part 2:
min(ABS(D2-B2:B))returns the minimum value from Part 1.
By filtering B2:B using the condition F2:F=G2 (part 1 = part 2), we can find the closest match in Google Sheets.
Formula 2 – Index-Match Approach
The second formula uses the Index-Match approach to find the closest match. It also uses Part 1 and Part 2 from the first formula.
In this approach, the MATCH function is used to find the position of the Part 2 output in the Part 1 output. Then, the INDEX function offsets B2:B by that many rows to get the closest match.
Finding the Closest Match in Google Sheets and Its Real-Life Use
Now that you know how to use these formulas, let’s consider a real-life example.
Imagine you’re a supplier of landscaping materials, and you have a stock of 20-40 mm white pebbles. The unit price of this item varies based on the order quantity. For example, for an order quantity of 10 kg, the unit price is $1.43 per kg.
If someone orders 45 kg, you want to find the closest unit price. By using one of the formulas mentioned above, you can easily find the closest match in Google Sheets.
Resources
If you want to explore more advanced features of Google Sheets, check out the following resources:
- Find the Past or Future Closest Date to Today in Google Sheets
- Query to Filter Closest Higher Date to Today in Google Sheets
- Nearest Match Greater Than or Equal to Search Key in Vlookup in Google Sheets
- Alternatives to the XMATCH Function in Google Sheets
- XLOOKUP Alternatives in Google Sheets – Search and Match Modes
- How to Lookup Latest Dates in Google Sheets [Array Formula]
- Extract the Earliest or Latest Record in Each Category Based on Timestamp
- Lookup Latest Value – How It Differs in Excel and Google Sheets
- Formula to Combine Rows and Get Latest Values in Google Sheets
Now you’re equipped with the knowledge to find the closest match in Google Sheets! Put these formulas to use and make your data analysis even more powerful. Happy sheeting!
