How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Thursday

How to Find Closest Match in Google Sheets

Posted by   on

To find the closest match in an array of numeric values in Google Sheets, we can use the Filter, Min, and Abs functions combo.


There is one more popular method among Excel users, which works in Google Sheets too.


In that method, we replace Filter with Index-Match. It has one issue, though!


What’s that?


If there are two closest matches, one higher value and one lower value, the formula will only return the lower value.


But the Filter, Min, and Abs combo can return both values. I’ll elaborate the same under the example below.


Formulas to Find Closest Match in Google Sheets


Sample Data: B1:B (where B1 contains column name)


Value to Evaluate in Sample Data: D2


Formula to Find Closest Match:


Insert either of the below formulas in cell E2.


Find Closest Match in Google Sheets - Using Filter

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 the above formulas, we can find the closest match of the value in cell D2 in the array B2:B.


Change the value in D2 to 500 and find the closest value using both the formulas.


The first formula will return two values. They are 400 and 600.


The second one will return only one value, i.e., 400.


Formula Explanations


Formula 1 – Filter Approach


It’s essentially a FILTER formula.


Syntax: FILTER(range, condition1, [condition2, …])


In this, we know the range to Filter is B2:B. What about argument condition1?


It’s ABS(D2-B2:B)=min(ABS(D2-B2:B))


We can split this condition1 into two parts.


Part_1: ABS(D2-B2:B)


Part_2: min(ABS(D2-B2:B)) or we can say min(part_1).


What do the part_1 and part_2 formulas return then?


We can test them in a Sheet (using ArrayFormula with them is a must outside Filter).


Part_1 Test in Cell F2:


=ArrayFormula(ABS(D2-B2:B))

Step - Absolute Difference and Its Purpose

The part_1 returns the difference of numbers in B2:B from D2, i.e., D2-B2:B.


Note:- I have used ABS, i.e., ABS(D2-B2:B), to return the absolute numbers (numbers without -ve sign).


By wrapping the above formula with MIN, we can get the min value difference, i.e., part_2.


That is the key to finding the closest match in Google Sheets.


Part_2 Test in Cell G2:


=ArrayFormula(MIN(ABS(D2-B2:B)))

Output: 10


It’s the third number in cell range F2:F (part_1).


If we filter B2:B (range) using the condition F2:F=G2 (part_1=part_2), we will get 50.


This way, we can find the closest match in Google Sheets.


Formula 2 – Index-Match Approach


If you could understand how to use the Filter, Min, and Abs combo to find the closest match in Google Sheets, the Index-Match way of doing the same is simple to learn.


Here also we will use the above part_1 and part_2 for explanation purposes.


In this approach, I have used the MATCH function to find the (relative) position of part_2 output in part_1 output.


Then using INDEX, I have managed to offset that many rows in B2:B to get the closest match.


Finding the Closest Match in Google Sheets and Its Real-Life Use


For example, you are one of the suppliers of landscaping materials, and you have a stock of 20-40 mm white pebbles.


Assume, the unit price of this item varies based on the order quantity.


I mean, for the order quantity of 10 kg, you sell the item at 1.43 per kg.


If the order quantity is 25 kg, then you sell the same item at 1.28 per kg.


The unit rate is 1.14 for 50 kg and 1 for 100 kg.


You want to find the closest unit price when someone orders 45 kg.


In this case, you can use one of my above formulas, which finds the closest match in Google Sheets. Here is how.


=filter(C2:C,ABS(D2-B2:B)=min(ABS(D2-B2:B)))

Find Closest Match of Quantity and Return Unit Rate

The formula finds the closest match of quantity and returns its unit price.


Resources

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!