Function Problems

Display Required Courses for a Selected Person in Excel Using Dynamic Array Formulas

Rana GarciaRana Garcia Nov 13, 2026 869 views

Question details

The user needs to show only the courses marked as required for a selected person from a large Excel table that contains names and course indicators.

Product
WPS Office Spreadsheets
Device & OS
not provided
Scenario
The worksheet has a large list of names and course requirement indicators, and the user wants to select a name and return only the course headers with a value of 1.
Observed behavior
The formula should return only the course names whose value is 1 for the selected person, or "No courses" if none match.
Before you start

Confirm the data layout before entering the formula: names should be in one column, such as A2:A600, course headers should be in one row, such as B1:H1, and required courses should be marked with the same value, such as 1.

Solution 1Recommended

Use LET, XLOOKUP, and FILTER to Return Required Course Headers

Best for Excel 365 or Excel 2021 users who want a single dynamic array formula that spills the matching course names automatically.

This formula looks up the selected person's row, compares the course indicator cells to 1, and passes the matching course headers to FILTER. It automatically spills results and does not require Ctrl+Shift+Enter.

1
Open your worksheet in WPS Spreadsheets

Open the workbook in WPS Office Spreadsheets and confirm that names are in A2:A600 and course headers are in B1:H1.

2
Enter the selected name

Type the person's name into cell J4, or your chosen lookup cell.

3
Enter the dynamic array formula

Select cell J6 and enter: =LET(r,XLOOKUP(J4,A2:A600,B2:H600),FILTER(B1:H1,r=1,"No courses"))

Use LET, XLOOKUP, and FILTER to Return Required Course Headers
Expected result: The formula spills the matching course headers into J6 and to the right. If no course has value 1, it returns No courses.
WPS Spreadsheets formula solution

Return Required Course Names for a Selected Person in WPS Spreadsheets

Open your workbook in WPS Office Spreadsheets and use a dynamic array formula to filter the course headers by the selected person's required-course indicator. WPS Spreadsheets supports Excel-compatible LET, XLOOKUP, and FILTER formulas, so the result updates automatically when the selected name changes.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the Excel-compatible workbook, and go to the worksheet containing the names and course indicators.
  2. 2. Check the data layout: Confirm that names are in one column, such as A2:A600, and course headers are in one row, such as B1:H1, where 1 means a course is required.
  3. 3. Enter the selected person: Type the person's name into cell J4, or any empty cell you want to use as the selection cell.
  4. 4. Insert the dynamic array formula: Click cell J6 and enter =LET(r,XLOOKUP(J4,A2:A600,B2:H600),FILTER(B1:H1,r=1,"No courses")).
  5. 5. Adjust ranges and press Enter: Update the ranges if your worksheet differs, then press Enter. WPS Spreadsheets will spill the matching course names automatically.
Dynamically returns only the courses marked as required for the selected personUses Excel-compatible LET, XLOOKUP, and FILTER formulasReads and writes .xlsx, .xls, and .csv files without conversion issuesNo manual filtering, sorting, or copying from large course tablesFree, lightweight interface with a familiar Excel-style ribbon
microsoft office alternative - wps office

Frequently Asked Questions

What does the value 1 mean in the course indicator range?

It identifies a course as required for that person. If your sheet uses another value, replace r=1 in the formula with that value.

How do I make the formula work when names or courses are in different columns?

Replace A2:A600 with your name range and B1:H1 with your course header range. In the legacy formula, also update the OFFSET width and COLUMN range to match the number of course columns.

Why does my LET or FILTER formula return #NAME? or #SPILL!?

#NAME? usually means your Excel version does not support LET or FILTER. #SPILL! means there is not enough empty space for the result; clear the cells below and to the right of the formula.

Can I return the required courses down a column instead of across a row?

Yes. Use the legacy INDEX, SMALL, and IF array formula in a cell and fill it down, or wrap the FILTER result with TRANSPOSE: =TRANSPOSE(FILTER(B1:H1,r=1,"No courses")).

Does this method work in WPS Office Spreadsheets?

Yes, WPS Office Spreadsheets supports LET, XLOOKUP, and FILTER in compatible Excel files. Open the workbook in WPS Spreadsheets and enter the same formula.