Find the Closest Value and Headers in a Two-Dimensional Excel Array
Question details
Find a table value closest to a target and return its corresponding row and column headers.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user needs to search a two-dimensional numeric table for the nearest value, or the nearest value that does not exceed the target.
- Observed behavior
- The desired result is the matching table value and its associated row and column headers.
Arrange numeric values in B2:E11, row headers in A2:A11, column headers in B1:E1, and the target in G2. The Excel formulas below require a version with TOCOL, XMATCH, LET, and FILTER; keep the data range free of blanks, text, and errors.
Find the nearest value in either direction with Excel formulas
Use this method when the closest value may be above or below the target.
The position formula flattens the table row by row and finds the first value with the smallest absolute difference from the target. If values are tied, it returns the first one in row-by-row order.
In G3, enter =LET(x,TOCOL(B2:E11,0,FALSE),XMATCH(MIN(ABS(x-G2)),ABS(x-G2),0)).
In G4, enter =INDEX(A2:A11,QUOTIENT(G3-1,COLUMNS(B2:E11))+1).
In G5, enter =INDEX(B1:E1,1,MOD(G3-1,COLUMNS(B2:E11))+1).
In G6, enter =INDEX(TOCOL(B2:E11,0,FALSE),G3).
Find the closest value that does not exceed the target
Use this method when the match must be smaller than or equal to the target, such as 0.1230 for a target of 0.1234.
Find the nearest table value in WPS Spreadsheet
Use a helper table in WPS Spreadsheet to compare each value with your target and return the matching row and column headers.
- 1. Open and arrange the table: Open your workbook in WPS Spreadsheet. Place numeric values in B2:E11, row headers in A2:A11, column headers in B1:E1, and the target in G2.
- 2. Calculate differences: In B15, enter =ABS(B2-$G$2), then fill the formula across to E15 and down to row 24. For values at or below the target only, use =IF(B2<=$G$2,$G$2-B2,1E+99) instead; first check that =COUNTIF(B2:E11,"<="&G2) is greater than zero.
- 3. Find each row minimum: In F15, enter =MIN(B15:E15), then fill the formula down through F24.
- 4. Find the matching position: In G3, enter =MATCH(MIN(F15:F24),F15:F24,0). In G4, enter =MATCH(INDEX(F15:F24,G3),INDEX(B15:E24,G3,0),0).
- 5. Return the headers: In G5, enter =INDEX(A2:A11,G3). In G6, enter =INDEX(B1:E1,1,G4). To return the matched value, enter =INDEX(B2:E11,G3,G4) in G7.

Frequently Asked Questions
What happens if two values are equally close to the target?
The Excel formulas return the first match when the table is scanned row by row, from left to right. The WPS helper-table method also selects the first matching row and then the first matching column.
How do I find a value strictly smaller than the target?
In the closest-smaller Excel formula in G3, change both <= comparisons to <. This excludes values equal to the target.
Why does the formula return an error in my version of Excel?
The Excel formulas require functions including TOCOL and XMATCH, which may not be available in older versions. Check for unsupported function names, and make sure B2:E11 contains only numbers without errors.
Can I change the table size?
Yes. Replace B2:E11 in the formulas with your numeric range, and adjust A2:A11 and B1:E1 so the row and column headers align with that range.




