Function Problems

Find the Closest Value and Headers in a Two-Dimensional Excel Array

Kushani NimanthikaKushani Nimanthika Nov 22, 2026 868 views

Question details

Find a table value closest to a target and return its corresponding row and column headers.

Find the Closest Value and Headers in a Two-Dimensional Excel Array
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.
Before you start

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.

Solution 1Recommended

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.

1
Find the matching position

In G3, enter =LET(x,TOCOL(B2:E11,0,FALSE),XMATCH(MIN(ABS(x-G2)),ABS(x-G2),0)).

2
Return the row header

In G4, enter =INDEX(A2:A11,QUOTIENT(G3-1,COLUMNS(B2:E11))+1).

3
Return the column header

In G5, enter =INDEX(B1:E1,1,MOD(G3-1,COLUMNS(B2:E11))+1).

4
Return the matched value

In G6, enter =INDEX(TOCOL(B2:E11,0,FALSE),G3).

Find table matches in WPS

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. 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. 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. 3. Find each row minimum: In F15, enter =MIN(B15:E15), then fill the formula down through F24.
  4. 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. 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.
Find the nearest value and its headers in a two-dimensional tableOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and a straightforward interface
microsoft office alternative - wps office

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.