WPS Spreadsheets Formula to Search an Employer List and Return Staff Names
Question details
The user needs a lookup formula that searches an employer list against a staff assignment table, matches partial employer names, and returns the assigned staff member for each listed employer while ignoring employers that are not assigned.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A Staff sheet contains staff members and their assigned employers, and an Employers sheet contains a list of employers to check. The user wants the Assigned Staff column on the Employers sheet to show the correct staff member for each employer.
- Observed behavior
- The formula should support partial text matches and multiple employer-to-staff assignments without creating a separate SEARCH function for every employer entry.
Arrange your data in a Staff table with columns for Staff Name and Employers, and keep the employer list in a separate Employers sheet. Confirm that the employer text is consistent enough for a case-insensitive SEARCH to match partial entries.
Return the first matching staff member with INDEX, AGGREGATE, and SEARCH
Use this solution when you need one assigned staff member for each employer on the list, including employer names that are stored as partial text.
This formula searches each employer value against the Staff table's Employers column, ignores errors with AGGREGATE, and returns the first matching Staff Name. It works without repeating SEARCH functions for every staff row.
On the Staff sheet, select the staff names and assigned employer cells, then press Ctrl+T and check My table has headers to create a table named Staff.
On the Employers sheet, click the cell under the Assigned Staff heading for the first employer row, then click the formula bar.
Type =IFERROR(INDEX(Staff[Staff Name],AGGREGATE(15,6,(ROW(Staff[Staff Name])-ROW(Staff[[#Headers],[Staff Name]]))/(ISNUMBER(SEARCH($A2,Staff[Employers]))),1)),"Not assigned") and adjust $A2 if your employer list starts in a different cell.
Press Enter, then drag the fill handle down through all employer rows. Unmatched employers display "Not assigned" instead of an error.
Return all matching staff names with FILTER in modern WPS Office or Microsoft 365
Use this solution when one employer may be assigned to multiple staff members and you want to see all matches in one cell or spill range.
Use WPS Office Spreadsheets to Build a Staff Lookup Formula
WPS Office Spreadsheets can complete this staff lookup with the same formulas you use in Excel, including SEARCH, ISNUMBER, INDEX, AGGREGATE, and FILTER where available. Open the workbook in WPS Office, apply the formula to the Assigned Staff column, and keep working in .xlsx format without losing compatibility.
- 1. Open your workbook in WPS Office: Download and install WPS Office, then open the .xlsx or .xls workbook that contains the Staff and Employers sheets.
- 2. Select the Assigned Staff column: Click the Employers sheet, then click the first cell under the Assigned Staff heading where you want the lookup result.
- 3. Enter the lookup formula: Type the INDEX and AGGREGATE formula =IFERROR(INDEX(Staff[Staff Name],AGGREGATE(15,6,(ROW(Staff[Staff Name])-ROW(Staff[[#Headers],[Staff Name]]))/(ISNUMBER(SEARCH($A2,Staff[Employers]))),1)),"Not assigned") in the formula bar.
- 4. Fill the formula down and save: Press Enter, drag the fill handle down through all employer rows, then save the workbook as .xlsx to keep the formulas and formatting.
Frequently Asked Questions
Why does the formula return #N/A instead of a staff name?
#N/A usually means no employer text in the Staff table contains the employer in the current row. Wrap the lookup formula in IFERROR and provide a fallback value such as "Not assigned" so unmatched employers display a clear result instead of an error.
Does this formula match partial employer names?
Yes. The SEARCH function performs a case-insensitive text search, so an employee assignment such as "Store A Main Street" matches a listed employer value of "Store A". This avoids creating separate SEARCH formulas for each staff member.
Can I return multiple staff names for one employer?
If multiple staff members are assigned to the same employer, use FILTER with TEXTJOIN in a version that supports dynamic arrays. FILTER can return all matching Staff Names, and TEXTJOIN can place them in one cell separated by commas.
Does WPS Office support the AGGREGATE and FILTER functions?
WPS Office Spreadsheets supports common Excel functions including SEARCH, ISNUMBER, and INDEX. AGGREGATE is available in many WPS Office versions, and FILTER is available in newer builds; if FILTER is not available, use the INDEX and AGGREGATE formula instead.
How do I prevent the formula from breaking when staff assignments change?
Convert the staff data to an Excel table and use structured references such as Staff[Staff Name] and Staff[Employers]. The table ranges expand automatically when new staff rows are added, so the lookup keeps calculating against the full updated list.
