Formula Errors

How to Use Excel LET Formula to Find MAT, Count Values, and Divide

Elise Williams Sep 27, 2026 869 views

Question details

The user needs a formula to dynamically find a row containing the text 'MAT', count applicable values in another column up to that row, retrieve an amount from a third column, and perform a division calculation.

How to Use Excel LET Formula to Find MAT, Count Values, and Divide

Product: Excel
Device & OS: not provided
Scenario: Calculating dynamic averages or division results based on the row position of a specific text identifier, ensuring the output is placed in the exact row matching the identifier.
Observed behavior: The user requires the division result to appear in column F of the exact row where 'MAT' is found, rather than in a static or incorrect location.

Before you start

Verify that your version of Excel or WPS Spreadsheet supports the LET and XMATCH functions (typically available in Office 365, Office 2021, and newer software versions). If unsupported, you will need to rely on traditional nested formulas.

Solution 1Recommended

Use the LET Function combined with XMATCH and COUNTA

Use the LET function to define variables for the row number, the count of items, and the dividend amount, making the calculation dynamic and easier to read.

The LET function assigns names to calculation results, preventing the need to calculate the same MATCH or INDEX multiple times in one formula.

Select the target cell

Click on the specific cell in column F on the row where you expect the calculation result to appear.

Enter the LET formula

Click into the formula bar and input the following formula: =LET(rw,XMATCH("MAT:",B:B),num,COUNTA(E2:INDEX(E:E,rw)),mat,INDEX(G:G,rw),mat/num)

Execute the calculation

Press the Enter key on your keyboard. The formula will locate 'MAT:' in column B, count the valid rows in column E, fetch the value from column G, and output the divided result (e.g., 6).

Use the LET Function combined with XMATCH and COUNTA

Adjusting Ranges: Make sure to adjust the range references (B:B, E:E, G:G) to match the actual data layout in your specific workbook.

Solution 2

Use Alternative Nested MATCH and INDEX Formulas

If you are using an older version of Excel that does not support the LET function, you can achieve the same result using traditional nested functions.

Easily Execute Advanced Formulas in WPS Spreadsheet

WPS Spreadsheet fully supports advanced modern array functions including LET, XMATCH, and XLOOKUP. You can easily build complex formulas, calculate datasets dynamically, and maintain formatting with a clean, familiar interface.

1. Download and Install: Download WPS Office for free from the official website and install it on your device.
2. Open Your Workbook: Launch WPS Spreadsheet and open your existing .xlsx workbook containing your data.
3. Apply the Formula: Select the target cell, input your LET formula in the formula bar, and press Enter to instantly calculate your dynamic division.

Full compatibility with Microsoft Excel formats (.xlsx, .xls)Supports modern functions like LET, XMATCH, and dynamic arraysFree and lightweight alternative to Microsoft OfficeBuilt-in error checking for complex nested formulas

microsoft office alternative - wps office

Frequently Asked Questions

Why does my LET formula return a #NAME? error?

The #NAME? error typically occurs if your current spreadsheet software does not support the LET or XMATCH functions. Ensure your software is updated to a newer version (such as Office 365, Excel 2021, or the latest WPS Office), or use standard nested functions like MATCH and INDEX instead.

How do I force the formula result to appear exactly in the row containing MAT?

Formulas only output results in the cell where they are entered; they cannot push data to other cells. To display the result in the row containing 'MAT', you must manually click and enter the formula directly into the target cell of that specific row (for instance, F12 if MAT is in row 12).

Can I search for text that doesn't have a colon at the end?

Yes. If your text is exactly 'MAT' instead of 'MAT:', simply update the search key in the XMATCH function. Modify that portion of the formula to read XMATCH("MAT",B:B).

Excel & Data Problems