How to Use Excel Tables, Dropdowns, and Lookups to Calculate FICA in WPS Office
Question details
The user needs to build a payroll workbook that selects an employee from a dropdown, retrieves that employee's FICA rate, and calculates FICA withholding from entered wages.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is creating an employee dropdown in a spreadsheet so employee information and the corresponding FICA rate are filled automatically, and withholding is calculated from entered wages.
- Observed behavior
- The workbook should use the selected employee from a dropdown to retrieve the matching FICA rate from a structured Excel table and calculate FICA withholding as wages multiplied by that rate.
Use structured Excel tables for employee details and FICA rates, with a unique employee ID or name as the lookup key. Make sure both tables contain matching employee identifiers before creating the dropdown and formulas.
Set Up Excel Tables, a Dropdown List, and XLOOKUP to Calculate FICA
Use this method when you want a centralized employee rate table and a low-maintenance dropdown that automatically calculates FICA withholding.
This keeps employee rates in structured Excel tables so the calculation sheet stays clean and reduces manual-entry errors.
In WPS Office Spreadsheets, create an Excel table named RateTable with columns Employee and FICA Rate, then enter each employee's name or ID and their FICA withholding rate.
Select the cell where users choose an employee, click Data > Validation, choose List, and set the source to the Employee column of RateTable.
In a wages cell enter the taxable wages; in the FICA result cell enter =Wages*XLOOKUP(SelectedEmployee,RateTable[Employee],RateTable[FICA Rate]) and press Enter.
Choose a different employee from the dropdown and confirm the formula retrieves the correct FICA rate and recalculates withholding.

Use VLOOKUP When XLOOKUP Is Not Available
Use this alternative if your version of WPS Office Spreadsheets does not include XLOOKUP.
Calculate FICA Withholding with WPS Office Spreadsheets
Use WPS Office Spreadsheets to build employee tables, create dropdown lists, and apply lookup formulas so FICA withholding is calculated automatically from entered wages.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open a new or existing .xlsx workbook where you want to build the FICA calculator.
- 2. Set up structured tables: Select your employee data and press Ctrl+T to create an Excel table with Employee and FICA Rate columns.
- 3. Add the employee dropdown: Click Data > Validation, choose List, and point the source to the Employee column in the rate table.
- 4. Apply the FICA lookup formula: In the result cell type =Wages*XLOOKUP(SelectedEmployee,RateTable[Employee],RateTable[FICA Rate]) and press Enter.
- 5. Verify the calculation: Select different employees from the dropdown to confirm the FICA rate and withholding amount recalculate correctly.

Frequently Asked Questions
Which formula should I use to calculate FICA from a selected employee?
Use =Wages*XLOOKUP(SelectedEmployee,RateTable[Employee],RateTable[FICA Rate]). This assumes Wages is the entered wage cell, SelectedEmployee is the dropdown cell, and RateTable has Employee and FICA Rate columns.
How do I create the employee dropdown in WPS Office?
Select the target cell, click Data > Validation, choose List, and set the source to the Employee column in your structured Excel table.
What if XLOOKUP is not available in my version?
Use a VLOOKUP or INDEX/MATCH formula instead. For example: =Wages*VLOOKUP(SelectedEmployee,RateTable,2,FALSE), with FICA Rate in the second column of the lookup range.
How should I structure the employee and FICA rate data?
Store it in structured Excel tables with a unique employee ID or name as the lookup key, and put the FICA rate in its own column so all formulas reference the table column.
How do I update the FICA calculation if a rate changes?
Change the rate in the FICA Rate column of the rate table; the dropdown and lookup formulas will automatically use the updated value when the workbook recalculates.




