Use XMATCH, BYROW, MAP, and LAMBDA to Return Years in Excel
Question details
Find a matching value in each data row and return the corresponding year from the header row using Excel dynamic array functions.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A table has years in a header row and values below them; the user wants the year associated with each row's minimum or a separately supplied lookup value.
- Observed behavior
- A formula should return one matching year per row as a spilled array.
Check that C4:I4 contains the year headers and C5:I16 contains the corresponding numeric values, with no blank cells in the data range. Make sure your Excel version supports the dynamic array functions used in your chosen formula and that the cells below the formula are empty.
Use BYROW and XMATCH to return the year for each row's minimum
Use this formula when the lookup value is the minimum within each data row.
Click an empty cell with at least 12 available cells below it for the results.
Enter =BYROW(C5:I16,LAMBDA(r,INDEX(C4:I4,1,XMATCH(MIN(r),r,0)))) and press Enter.
Review the spilled results: XMATCH locates the minimum in each row, and INDEX returns the year in the corresponding column of C4:I4.

Use MAP and XLOOKUP for a separate list of lookup values
Use this approach when the values to find are already listed separately, with one value for each data row.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Excel files, making it easier to move existing workbooks. Check formula compatibility before relying on Excel-specific dynamic array functions.

Frequently Asked Questions
Can I use XLOOKUP instead of XMATCH with BYROW?
Yes. In an empty cell, enter =BYROW(C5:I16,LAMBDA(r,XLOOKUP(MIN(r),r,C4:I4))) to return the year for each row's minimum.
Why does the formula return a spill error?
A spilled array needs empty cells for its results. Clear any content blocking the output range, then re-enter the formula outside an Excel table if necessary.
What happens if two columns share the minimum value?
The XMATCH and XLOOKUP formulas shown return the year for the first matching column in the row.
Why does the MAP formula return #N/A?
The lookup value may not exist in its corresponding data row. Check that M5:M16 is ordered to match C5:I16 and that the values match the row entries.
Will these formulas work in every version of Excel?
No. Your Excel version must support the functions used in the formula, including BYROW and LAMBDA or MAP and CHOOSEROWS. Check your version's function availability if Excel does not recognize a function.




