Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting Color Scales Examples in Excel
  • Get column index in Excel Table
  • Number and Text Filters Examples in Excel
  • Conditional Formatting Data bars Examples in Excel
  • How to create running total in an Excel Table

References

  • How to retrieve first match between two ranges in Excel
  • Last row number in range
  • Count unique text values with criteria
  • Extract all partial matches in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Validations

  • Excel Data validation only dates between
  • How To Create Drop-down List in Excel
  • Excel Data validation must contain specific text
  • Data validation must not exist in list
  • Prevent invalid data entering in specific cells

Find closest match in Excel

by

This tutorial shows how to Find closest match in Excel using the example below;

Formula

{=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0))}

Explanation

To find the closest match with a lookup value and numeric data, you can use an array formula based the INDEX, MATCH, ABS and MIN functions. In the example shown, the formula in E5 is:

{=INDEX(data,MATCH(MIN(ABS(data-E4)),ABS(data-E4),0))}

where “data” is the named range B5:B14, and E4 contains a lookup value.

Note: this is an array formula and must be entered with control + shift + enter.

How this formula works

At the core, this is an INDEX and MATCH formula where MATCH locates the position of the closest match and feeds that postion into INDEX. INDEX then returns the value at that position. All of the hard work is done inside the MATCH function, which is configured like this:

MATCH(MIN(ABS(data-E4)),ABS(data-E4),0)

Inside MATCH, this expression calculates the differences between the lookup value in E4 and the values in the named range data:

data-E4

This is an array expression, and it returns and array result like this:

{-18;-6;-2;1;6;8;10;11;13;19}

The ABS function is then used to convert negative values to positive:

{18;6;2;1;6;8;10;11;13;19}

These values represent the difference between the lookup value and the values in data. We are looking for the closest match,  so we use the MIN function to return the smallest value. In this case, the smallest value is 1, and this becomes the lookup value inside MATCH.

Worked Example:   How to get last row in numeric data in Excel

The lookup array is calculated in a similar way. The expression:

ABS(data-E4)

returns the following array to MATCH as the lookup array:

{18;6;2;1;6;8;10;11;13;19}

The last argument inside MATCH is match_type, which is set to zero to force an exact match.

Worked Example:   Sum last n columns in Excel

Finally, with these values, the MATCH function returns the position of 1 inside the array, which is 4. The position is fed into INDEX as the row argument:

=INDEX(data,4)

The INDEX function then returns the value at that position, which is the date July 26, 2018.

Worked Example:   Round a number to n significant digits in Excel

Note: in the event of a tie, this formula will return the first match.

Click link for more examples on how to find closest match in Excel.

Post navigation

Previous Post:

Weighted average in Excel

Next Post:

Win loss points calculation in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel NOT Function
  • How to use Excel OR Function
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • IF, AND, OR and NOT Functions Examples in Excel

Date Time

  • Next biweekly payday from date in Excel
  • Excel Date & Time Functions Example
  • Get project end date in Excel
  • Display Date is same month in Excel
  • Get days before a date in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • If cell contains one of many things in Excel
  • Group arbitrary text values in Excel
  • Group numbers with VLOOKUP in Excel
  • Group times into 3 hour buckets in Excel

General

  • 44 Practical Excel IF function Examples
  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • How to calculate percent of students absent in Excel
  • Automatically fill series of cells in Excel using AutoFill
  • Excel Operators
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning