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

Data Analysis

  • How To Create Pareto Chart in Excel
  • Create Scatter Chart in Excel
  • How to create running total in an Excel Table
  • Move chart to a separate worksheet in Excel
  • Add Outline to Data in Excel

References

  • How to retrieve first match between two ranges in Excel
  • How to get relative column numbers in a range in Excel
  • How to get last row in numeric data in Excel
  • Extract data with helper column in Excel
  • How to use Excel LOOKUP Function

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation must not contain
  • Excel Data validation date in specific year
  • Excel Data validation only dates between
  • Excel Data validation don’t exceed total

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.

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.

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.

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:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

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

  • Invoice status with nested if in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel AND Function
  • SWITCH function example in Excel
  • IF with boolean logic in Excel

Date Time

  • Extract date from a date and time in Excel
  • Display Date is workday in Excel
  • How to enter Today’s Date or Static Date and Time in Excel
  • Sum through n months in Excel
  • Calculate number of hours between two times in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • How to randomly assign people to groups in Excel
  • Group times into unequal buckets in Excel
  • Calculate conditional mode with criteria in Excel
  • Running count group by n size in Excel

General

  • How to calculate percent sold in Excel
  • How to generate random number weighted probability in Excel
  • How to fill cell ranges with random number from fixed set of options in Excel
  • How to calculate project complete percentage in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning