Return Vacation Days by Position and Service Years in Excel
Question details
Return an employee's vacation allowance using their position and years of service as lookup criteria.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An employee's position and years of service must be matched against a vacation allowance table.
- Observed behavior
- The worksheet should display the vacation days associated with the matching position and exact service value.
Check that position is in C2, years of service is in D2, and the lookup table contains positions in I2:I38, service values in J2:J38, and vacation days in K2:K38. Make sure the position text and service values match the table entries.
Use INDEX and MATCH to Find the Vacation Allowance
Use this formula when the lookup table has an exact position and service value for each allowance.
The formula matches both criteria in the same table row and returns the corresponding value from column K. If more than one row matches, it returns the first match.
In Excel, click the cell where you want the employee's vacation days to appear.
Enter =INDEX($K$2:$K$38,MATCH(1,(C2=$I$2:$I$38)*(D2=$J$2:$J$38),0)). Adjust the cell ranges if your table is in a different location.
Press Enter in Microsoft 365 or Excel 2021 and later. In older Excel versions, confirm it with Ctrl+Shift+Enter where supported.
Drag the result cell's fill handle down to calculate allowances for other employees. The lookup ranges remain fixed while C2 and D2 change by row.

Look Up Vacation Days in WPS Spreadsheet
Use the same INDEX and MATCH formula in WPS Spreadsheet to return vacation days from a table of positions and service values.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and locate the employee data and vacation allowance table.
- 2. Select a result cell: Click the cell where you want to display the vacation allowance for the employee in row 2.
- 3. Enter the lookup formula: Enter =INDEX($K$2:$K$38,MATCH(1,(C2=$I$2:$I$38)*(D2=$J$2:$J$38),0)) and adjust the references if your table uses different cells.
- 4. Confirm and fill down: Press Enter, or use Ctrl+Shift+Enter if your version requires array formula entry. Drag the fill handle down to apply the formula to other employee rows.

Frequently Asked Questions
Why does the formula return #N/A?
No table row matches both C2 and D2 exactly. Check the position text and confirm that the service value in D2 matches a value in J2:J38.
Can this formula use service-year ranges?
Not as written. It requires an exact service value; a table based on minimum service thresholds needs a different lookup approach.
What happens if two table rows have the same position and service value?
MATCH finds the first matching row, so INDEX returns the vacation days from that row. Review columns I through K for duplicate combinations.
Why are dollar signs used in the lookup ranges?
The dollar signs keep I2:I38, J2:J38, and K2:K38 fixed when you drag the formula down. The employee references C2 and D2 change for each row.




