Function Problems

How to Find the First, Second, and Third Closest Values in Excel

Ayan Masood Oct 22, 2026 869 views

Question details

Extract the top three values that are closest to a specific target number while keeping their associated names intact.

How to Find the First, Second, and Third Closest Values in Excel

Product: Excel
Device & OS: not provided
Scenario: Looking up numeric data in a table to identify entries with scores nearest to a specified target value, especially when the lookup data is structured across multiple columns.
Observed behavior: Standard lookup functions like VLOOKUP or XLOOKUP only return exact or approximate matches natively, requiring complex reshaping and absolute difference calculations to rank closest multiple results.

Before you start

Ensure you are using a modern version of Excel (Microsoft 365 or Excel 2021) that supports dynamic array functions such as TAKE, SORT, HSTACK, and TOCOL to use the recommended approach.

Solution 1Recommended

Use Dynamic Array Formulas to Extract the Closest Values

Combine data into a single spilled array, calculate the absolute difference, and sort it dynamically to fetch the first three matches.

By calculating the absolute difference between your scores and the target, you can dynamically sort the data from the smallest difference to the largest. Modern array functions handle the reshaping of two-column data seamlessly.

Select the Output Range

Click on an empty cell where you want the resulting names and their closest scores to appear. Make sure there is enough space below and to the right for the array to spill.

Enter the Dynamic Array Formula

Input the formula that reshapes the data and sorts by the absolute difference. For example: =TAKE(SORT(HSTACK(TOCOL(HSTACK(Table1[Student],Table1[Student])),TOCOL(Table1[[Score 1]:[Score 2]]),ABS(TOCOL(Table1[[Score 1]:[Score 2]])-target)),3),3,2)

Execute the Formula

Press Enter. The formula will automatically spill the top three results displaying the closest values alongside the associated names.

Use Dynamic Array Formulas to Extract the Closest Values

Handling Tied Values: If multiple entries share the exact same difference from the target value, they will be returned in the order they appear. For advanced tie handling, consider grouping the data using Power Query.

Solution 2

Restructure Table and Use INDEX/MATCH

If you cannot use dynamic array formulas, restructure your lookup range into simple columns and use a classic INDEX and MATCH array formula.

Solve complex data problems effortlessly

Find Closest Values Easily Using WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array functions and standard lookup formulas, allowing you to extract nearest matches without compatibility issues.

1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your scores and targets.
2. Apply the advanced array formula: Select an empty cell and input your TAKE, SORT, and HSTACK formula to calculate the absolute differences.
3. Generate the results: Press Enter to spill the results. WPS Spreadsheet automatically formats and displays the top three closest matches.

Fully supports dynamic array functions like SORT, HSTACK, TOCOL, and TAKE.100% compatible with Microsoft Excel formulas, charting, and formatting.Lightweight, fast, and free to use for daily data analysis and processing.

microsoft office alternative - wps office

Frequently Asked Questions

Can I find the closest value without dynamic array functions?

Yes, you can use an array formula combining INDEX, MATCH, MIN, and ABS. However, returning the second or third closest matches requires using the SMALL function instead of MIN, and it does not natively group results as elegantly as dynamic arrays.

What happens if my target value has an exact match in the dataset?

The absolute difference will be calculated as zero. Since the formula sorts by the smallest absolute difference, the exact match will automatically be returned as the first closest value.

How do I handle duplicate values when searching for the second or third closest?

If there are identical scores that have the exact same difference from your target, the SORT function will typically pull them sequentially. If you need robust tie-handling to combine names or group duplicates, using Power Query to calculate absolute differences and group results is recommended.

Excel & Data Problems