Find the Closest Match in Excel using Formulas

Excel functions can be extremely powerful if you get a hang of combining different formulas. Things that might have seemed impossible would suddenly look like a cake walk.

One such example is to find the closest match of a lookup value in a dataset in Excel. All the awesome excel functions at our disposal – such as MATCH, VLOOKUP, HLOOKUP – can give you an exact match or an approximate match, but none of these by itself can find the closest match in Excel.

In this tutorial, I will show you how to find the closest match in Excel using a combination of functions.

Find the Closest Match in Excel

Here is a sample data set where I need to find the employee name that has the work experience closest to the desired value.

Find the Closest Match in Excel

Here is the formula (in cell F3) that can find the closest match in Excel (since this is an array formula, use Control + Shift + Enter, instead of Enter).

=INDEX($B$3:$B$16,MATCH(MIN(ABS(E3-C3:C16)),ABS(E3-$C$3:$C$16),0))
 How This Works
  • MATCH(MIN(ABS(E3-C3:C16)),ABS(E3-$C$3:$C$16)
    • This part of the formula identifies the position of the value that is closest to the lookup value.
      • The ABS(E3-$C$3:$C$16) part of the formula converts the range C3:C16 into an array where we have subtracted the value in E3 from each element. This would give an array of positive or negative numbers (based on the value). Now we use ABS function to get the absolute value.
      • MIN(ABS(E3-C3:C16)) finds the minimum value in this array, which would be the value that is closest to the lookup value. This also becomes our lookup value in the MATCH function.
  • INDEX($B$3:$B$16,MATCH(MIN(ABS(E3-C3:C16)),ABS(E3-$C$3:$C$16),0))
    • The MATCH part of the function returns the position of the closest match, which is used by the INDEX function to fetch the value.

Find the Closest Match in Excel Formula

Note: In case there are duplicates, this formula would fetch the first matching value.

Download the Example file from here
Download File Pic

Suggested Tutorials:
  • PhilBoogie says:

    Is there a way to find the closest match from multiple entries? I have a running race coming up, and our group thought it’d be great to have everyone make a guess to the net times the other runners will achieve.

    Thanks much!

  • John Plant says:

    This is brilliant Sumit. Thanks.

  • Meni Porat says:

    Hi,
    Good example and explanation.
    I think that you should specify somewhere that this is an array formula, so that the CSE (CTRL+SHIFT+ENTER) keys must be pressed simultaneously instead of the ENTER key.

  • Dave Bruns says:

    Nice example! I’m probably missing something, but what is the logic of making both the range with employee names, and the range used to create the lookup array in MATCH absolute referernces?

    • Sumit Bansal says:

      Hi Dave.. There is no specific reason for it. I have this habit of using absolute references, but its perfectly fine to use relative as well

      • Dave Bruns says:

        OK, good. I’d only had one cup of coffee when I first looked at this, so was worried that I was failing to grasp something basic 🙂 Again, cool formula.

  • >