Learn more:- https://www.exceltutorial.net/

Lookup Playlist:- https://youtube.com/playlist?list=PLhvn6aQRdimXjKB1EOoRWznYZtqRe7B2z

If you need to find the closest match to a target value in Excel, index and match functions will do just that. The index function finds the relative position of a given item by sorting your data into ascending or descending order.

You then use INDEX with MATCH to return an exact match for the index number returned by INDEX, as long as it is within one row of your range argument.

If you want an approximate match instead of an exact match, use ABS and MIN together as well as VLOOKUP to find matches outside your range argument.

We will be using ABS function to find the closest match of values in a column.

lets see the syntax of ABS function;

ABS(number)

The number argument is the value you want to find the absolute value of.

As shown in Video, index numbers are used for exact matches and VLOOKUP will return an approximate match. This combination can be also used with ABS function; index values + abs function+lookup table (range).

Then, we will use MIN function to find the closest or minimum value to the ABS result.

Let's see the syntax of MIN Function below;

MIN(number, number_list)

The MIN function returns the minimum value from a range of numbers.

number is the value you want to find the minimum of and number_list is a list of numbers.

Now, we will use MATCHfunction to locate both cells of MIN and ABS results.

Let's Learn Syntax of MATCH Function;

MATCH(lookup_value, lookup_array, [match_type])

The MATCH function finds the relative position of a given item in a sorted array.

lookup_value is the value you want to find the position of and lookup_array is the range of cells that contains your data.

[match_type] specifies how Excel should match values when searching for an approximate match.

So now we know the position of those cells using MATCH function and now we will locate the values using INDEX function.

Let's Learn Syntax of INDEX function;

INDEX(array, row_num,[column_num])

The index function returns a value or reference from an array of information.

Array is the range containing your data and row number is the index number returned by MATCH function (ABS result). Column num is column position 0 for both MIN and ABS functions. As you can see in Video; this combination will return the closest match to our target value/approximate match depending on what we want to find.

Now do browse table of content below to see through timelines;

00:00 Introduction

00:42 Closest Match In Excel

05:40 Conclusion

Other Playlists:-

Cell References in Excel - https://youtube.co ... https://www.youtube.com/watch?v=e3NTtCeTyzE