Function Problems

Use XMATCH, BYROW, MAP, and LAMBDA to Return Years in Excel

Ayan MasoodAyan Masood Nov 18, 2026 869 views

Question details

Find a matching value in each data row and return the corresponding year from the header row using Excel dynamic array functions.

Use XMATCH, BYROW, MAP, and LAMBDA to Return Years in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select an output cell

Click an empty cell with at least 12 available cells below it for the results.

2
Enter the formula

Enter =BYROW(C5:I16,LAMBDA(r,INDEX(C4:I4,1,XMATCH(MIN(r),r,0)))) and press Enter.

3
Check the results

Review the spilled results: XMATCH locates the minimum in each row, and INDEX returns the year in the corresponding column of C4:I4.

Use BYROW and XMATCH to return the year for each row's minimum
Duplicate minimum values: If a row contains the same minimum more than once, XMATCH returns the first matching position.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for working with tables and formulasMove existing workbooks without starting from scratchUse a free, lightweight office suite
microsoft office alternative - wps office

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.