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

Data Analysis

  • How to count table columns in Excel
  • How to Create Thermometer Chart in Excel
  • How to Create Column Chart in Excel
  • How to create running total in an Excel Table
  • How to Create Gantt Chart in Excel

References

  • How to use Excel ROWS Function
  • Left Lookup in Excel
  • How to get relative row numbers in a range in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation must begin with
  • Excel Data validation require unique number
  • Excel Data validation only dates between
  • Excel Data validation specific characters only
  • Excel Data validation no punctuation

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

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

  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • How to return blank in place of #DIV/0! error in Excel
  • IF with wildcards in Excel
  • How to use Excel TRUE Function
  • Check multiple cells are equal in Excel

Date Time

  • Calculate total hours that fall between two times in Excel
  • Display Days until expiration date in Excel
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • Get last day of month in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign people to groups in Excel
  • Group times into unequal buckets in Excel
  • Map text to numbers in Excel

General

  • Excel Autofill Cell Ranges, Copy, Paste
  • Count cells that do not contain many strings in Excel
  • Subtotal by color in Excel
  • Basic numeric sort formula in Excel
  • Excel Ribbon Quick Overview For Beginners
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning