Function Problems

Return Vacation Days by Position and Service Years in Excel

Nimra MalikNimra Malik Nov 18, 2026 868 views

Question details

Return an employee's vacation allowance using their position and years of service as lookup criteria.

Return Vacation Days by Position and Service Years in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

In Excel, click the cell where you want the employee's vacation days to appear.

2
Enter the formula

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.

3
Confirm the formula

Press Enter in Microsoft 365 or Excel 2021 and later. In older Excel versions, confirm it with Ctrl+Shift+Enter where supported.

4
Fill down

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.

Use INDEX and MATCH to Find the Vacation Allowance
Exact matches are required: A #N/A result means no row matches both criteria. This formula does not automatically find an allowance based on service-year thresholds.
Calculate vacation allowances

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheet and locate the employee data and vacation allowance table.
  2. 2. Select a result cell: Click the cell where you want to display the vacation allowance for the employee in row 2.
  3. 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. 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.
Match position and years of service in one formulaOpen and edit Microsoft Excel XLSX and XLS filesFill the formula down for multiple employeesUse a familiar spreadsheet interface for free
microsoft office alternative - wps office

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.