Welcome! Lets start your excel journey..

Welcome! Register Now to Unlock Exclusive Excel Tutorials

Get Started
  • Exclusive Playlists
  • Downloadable Videos
  • No Ads!
  • Exclusive Templates

How to Find the Closest Match to a Target Value in Excel

Admin
Duration: 6:13
Submitted: 3 months ago
Views: 134

Comments (0)

Link to this video:

Description: If you need to find the closest match to a target value in a data column, you can use the INDEX, MATCH, ABS and MIN functions in Excel. The VLOOKUP function can also be used to find an approximate match. In this tutorial, we will show you how to use these functions to find the closest match. Let's get started! To discover through timelines, go now to the Table of Contents below and look at the timings; 00:00 Introduction 00:42 Closest Match In Excel 05:40 Conclusion We are going to use 3 functions in this formula; Index Function Match Function ABS function Now lets discuss the syntax of all these functions! The INDEX function returns the value of a cell in a range, given the row and column numbers. The Match function searches for a specified value in a range of cells, and then returns the relative position of that value. The ABS function returns the absolute value of a number. And finally, the MIN function returns the smallest number in a set of values. Now, Let's create our own formula! =INDEX(Range, MATCH(MIN(ABS((Target-Range))), ABS((Target-Range)), 0), Column_Number) For example: =INDEX($C$15:$C$21,MATCH(MIN(ABS((C$13-Table))), ABS((C$13-Table)), 0),0) range: The range of cells which you want to return the closest value from. In this example, we have used C15:C21 as our range of data. target: This is the target value that we are trying to match. In this example, we have used C13 as our target value. column_number: This is the column number in the range which you want to return a value from. If this argument is omitted, it will default to column # one within the range defined by your first argument (range). The INDEX function returns the value of a cell in a range, given the row and column numbers. The Match function searches for a specified value in a range of cells, and then returns the relative position of that value. The ABS function returns the absolute value of a number. And finally, the MIN function returns the smallest number in set of values.