How to Return an Office Based on Name and Effective Date in WPS Office Spreadsheets
Question details
The user needs a spreadsheet formula that returns the office assigned to a specific person on a specific date, based on historical records that may include multiple office changes.
- Product
- WPS Office Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- The user has source data with Name, Office, and Date columns and wants to look up the office that applied to a person on a given effective date, including cases where employees changed offices over time.
- Observed behavior
- The lookup should return the latest matching office record for the person where the record date is on or before the effective date; if no matching record exists, it should return a blank.
Make sure the source records are in a table or range with Name, Office, and Date columns, and that all dates are real date values rather than text. In this guide, the lookup name is in B2 and the lookup effective date is in A2.
Use the TAKE and FILTER dynamic array formula
Best for current WPS Office or Excel versions because it handles the historical lookup without Ctrl+Shift+Enter and updates automatically with table data.
The FILTER function returns all rows for the person whose date is on or before the effective date, and TAKE then selects the last row from that filtered array. If no row qualifies, FILTER returns the configured empty-string fallback.
Click the cell where you want the returned office to appear, for example C2 on the same sheet.
Type =TAKE(FILTER(Table1[Office],(Table1[Name]=B2)*(Table1[Date]<=A2),""),-1) and press Enter.
If your table is not named Table1 or your lookup cells are not A2 and B2, update the table name and cell references to match your workbook.
Copy the formula down for other lookup rows; a blank means no office record existed on or before that effective date.
Use an INDEX, LARGE, and IF array formula in older versions
Use this when TAKE or FILTER is not available in your WPS Office or Excel version.
Return the Correct Office by Name and Date in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets and use modern lookup formulas or a legacy array formula to retrieve the office that was active for a person on a specific date. WPS Office handles Excel formulas and .xlsx data, so you can apply the same steps without changing your workflow.
- 1. Open WPS Office Spreadsheets: Launch WPS Office, open the .xlsx workbook that contains the Name, Office, and Date records.
- 2. Prepare the source data: Convert the source range into a table named Table1, or note the exact sheet and ranges if you are using the legacy array formula.
- 3. Enter the lookup formula: In the result cell, type the recommended TAKE/FILTER formula or the legacy INDEX/LARGE/IF formula, depending on your version.
- 4. Confirm and copy the formula: Press Enter for the dynamic formula, or Ctrl+Shift+Enter for the legacy array formula, then copy it to the remaining lookup cells.
- 5. Verify the returned office: Check that each row returns the latest matching office on or before the effective date; a blank means no matching record existed.
Frequently Asked Questions
Why does the formula return a blank instead of an office?
It usually means no source row has a date on or before the effective date for that person, or the name and date values do not match exactly. Check for extra spaces, text-formatted dates, or mismatched table and range references.
Does the TAKE and FILTER formula work when employees change offices multiple times?
Yes. It filters all office records for the person up to the lookup date and returns the last row. For best results, sort the source table by Name and Date ascending so the last qualifying row is the most recent.
What if my WPS Office version does not support TAKE or FILTER?
Use the legacy INDEX, LARGE, and IF array formula in this guide. Enter it with Ctrl+Shift+Enter and adjust the sheet ranges to match your source data.
Can I replace the office column with another column, such as department or manager?
Yes. Change Table1[Office] to the relevant structured reference, or change the INDEX return range in the legacy formula while keeping the name and date condition ranges the same.
