Replace Long Excel OR Formulas with a Date Lookup Table
Question details
Replace a long OR formula used for date-based pay rates with a scalable lookup-table formula.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to assign different pay rates for multiple users based on service-date periods and add more periods easily.
- Observed behavior
- Adding a third date range to the OR formula causes a "too many arguments" error.
Create a rate table with one row per user and one column for each rate period start date. Make sure the date headers are real dates and are sorted from oldest to newest.
Use INDEX and MATCH with a Date-Based Rate Table
Use this scalable method when rates depend on both the user name and the most recent applicable service-date start period.
A lookup table removes the need to extend nested OR formulas whenever a new pay-rate period or user is added. The first MATCH locates the user row, while the second approximate MATCH finds the latest period start date that is on or before the service date.
Enter user names in B3:B5. Enter the starting date for each pay-rate period in C2:F2, arranged in ascending order from left to right.
Enter the applicable rate for each user and date period in C3:F5. Each row must correspond to the user name in column B.
In the pay-rate result cell, enter =INDEX($C$3:$F$5,MATCH([@User],$B$3:$B$5,0),MATCH([@Date],$C$2:$F$2,1)). Replace [@User] and [@Date] with the relevant user-name and service-date cell references if your data is not in an Excel table.
Add a new, later start date to the next column of the date headers and enter each user's new rate beneath it. Expand the formula ranges if the new column is outside $C$2:$F$5.
Create a Scalable Date-Based Pay Rate Lookup in WPS Spreadsheet
WPS Spreadsheet supports INDEX and MATCH formulas for looking up a user's rate by service date. Build a rate matrix with sorted effective dates, then use one formula that continues to work as you add rate periods and users.
- 1. Open the rate sheet in WPS Spreadsheet: Open the workbook in WPS Office and select the worksheet containing the user names, effective dates, and pay rates.
- 2. Build the lookup matrix: Place user names in the first column of the matrix, put effective start dates across the top row, and enter the matching rates in the body of the table.
- 3. Sort effective dates from oldest to newest: Select the date-header cells and arrange them in ascending date order so the approximate MATCH can identify the current rate period.
- 4. Insert the INDEX and MATCH formula: Select the rate result cell and enter =INDEX($C$3:$F$5,MATCH([@User],$B$3:$B$5,0),MATCH([@Date],$C$2:$F$2,1)).
- 5. Fill the formula for additional records: Use the fill handle to copy the formula down for more users or service-date records, then expand the lookup ranges when adding new rate columns.
Frequently Asked Questions
Why does my Excel OR formula show too many arguments?
OR can become difficult to maintain when many date-range tests are combined, especially inside nested IF formulas. A lookup table avoids adding more logical conditions for every new rate period.
Why must the effective-date headers be in ascending order?
MATCH with a match type of 1 performs an approximate lookup. It needs dates sorted from oldest to newest so it can return the last start date that is less than or equal to the service date.
What happens if the service date is before the first date in the lookup table?
The approximate MATCH returns an error because no start date is less than or equal to the service date. Add an earlier default effective date or use IFERROR to handle those records.
Can I add more users and pay-rate periods later?
Yes. Add users as new rows and rate periods as new columns. Update the INDEX and MATCH ranges, or convert the source range to an Excel table so references can expand more easily.
