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

by

In this tutorial, we will show you how to find the closest match to a target value in a column of data using different lookup functions in Excel. Specifically, we will use the INDEX, MATCH, ABS, and MIN functions to find the student with the highest score closest to 100.

Step-by-Step Instructions

  1. In our example data, we have a list of student names and their scores. To find the closest match to 100, we need to subtract each score from 100.
    We can use the formula “=B2:B13-F3” (where B2 is the cell containing the first student’s score and F3 contain the target value of 100) to do this.
  2. This will give us an array of numbers showing how close each student’s score is to 100. However, we don’t want negative numbers, so we will use the ABS function to take the absolute value of each number.
    The formula for this would be “=ABS(B2:B13-F3). This will give us an array of positive numbers, which we can now use to find the closest match.
  3. Now that we have an array of positive numbers showing how close each student’s score is to 100, we can use the MIN function to find the smallest number in the array.
    The formula for this would be “=MIN(ABS(B2:B13-F3))”, where B2:B13 is the range of cells containing the student scores. This will give us the smallest value, the closest match to 100.
  4. Finally, we can use the INDEX and MATCH functions to find the student name corresponding to this closest match.
    Let’s locate the cell position of the closest match first. We can do this by using the MATCH function to find the position of the smallest value in the array. The formula for this would be: “=MATCH(MIN(ABS(B2:B13-F3)),ABS(B2:B13-F3),0)”. Here, the third argument, 0, tells the MATCH function to look for an exact match.
  5. Now that we have located the cell position of the closest match, we can use the INDEX function to find the corresponding name. The formula for this would be “=INDEX(A2:A13,(MATCH(MIN(ABS(B2:B13-F3)),ABS(B2:B13-F3),0))”.
    Here, the INDEX function looks for the student name in column A, which is located at the same position as the closest match.

Conclusion

In this tutorial, we have shown you how to use the INDEX, MATCH, ABS, and MIN functions in Excel to find the closest match to a target value in a column of data. We hope that you have found this tutorial helpful.

Related Posts: