How to Create a Banded Fee Formula in WPS Office Excel
Question details
Create a WPS Office Excel formula that returns a fee from tiered personnel count bands using an approximate lookup.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is building a pricing model where fees depend on personnel count ranges such as 0–1,000, 1,001–2,000, and higher.
- Observed behavior
- The desired formula should automatically return the correct band fee when a personnel count is entered.
Prepare a lookup table with the lower limit of each personnel band in one column and the corresponding fee in the next column. Ensure the lower-limit column is sorted in ascending order so that VLOOKUP approximate match returns the correct band.
Create a band lookup table and apply a VLOOKUP approximate-match formula
Use this method when fees are tiered by personnel count and you want WPS Office Excel to automatically select the correct fee band.
VLOOKUP with TRUE as the fourth argument performs an approximate match. It finds the largest lower limit that is less than or equal to the lookup value, which is ideal for banded pricing.
The first column of the lookup table must be sorted in ascending order, and the formula returns the fee from the second column.
Open your WPS Office Excel workbook and enter the lower limit of each personnel band in one column and the corresponding fee in the next column, for example D2:E7.
Type the personnel count you want to price in cell A2, or select the cell that contains it.
In the fee cell, enter =VLOOKUP(A2,$D$2:$E$7,2,TRUE) and press Enter.
Drag the fill handle down or across to apply the same formula to additional personnel count cells.
Create a Banded Fee Formula with VLOOKUP in WPS Office Excel
Use WPS Office Excel to set up a sorted lookup table and a VLOOKUP approximate-match formula. WPS Office supports standard Excel functions, so you can calculate tiered fees by personnel count without changing your workflow.
- 1. Open your workbook in WPS Office Excel: Launch WPS Office and open the .xlsx file containing your fee schedule and personnel counts.
- 2. Build the band lookup table: Enter band lower limits and fees in two columns, such as D2:E7, and sort the first column ascending.
- 3. Enter the VLOOKUP formula: In the cell where the fee should appear, type =VLOOKUP(A2,$D$2:$E$7,2,TRUE).
- 4. Verify the returned fee: Check that sample counts in each band return the expected fee, then copy the formula to all rows.
Frequently Asked Questions
Why should I use TRUE instead of FALSE in VLOOKUP for a banded fee structure?
TRUE enables approximate match, which finds the largest lower limit that is less than or equal to the personnel count. FALSE would require an exact match to a lower limit and is not suitable for tiered ranges.
Does the lookup table need to be sorted?
Yes. The first column of the lookup table must be sorted in ascending order. Otherwise VLOOKUP approximate match may return the wrong band.
Can I use this formula in WPS Office the same way as Microsoft Excel?
Yes. WPS Office Excel supports VLOOKUP approximate match and works with .xlsx files, so the formula behaves the same way as in Microsoft Excel.
What happens when the personnel count is higher than the highest band?
VLOOKUP approximate match returns the fee associated with the highest lower limit. Set the highest lower limit to represent the open-ended top band.
Can I replace nested IF formulas with VLOOKUP for banded pricing?
Yes. A lookup table with VLOOKUP is easier to maintain than long nested IF formulas, especially when band limits or fees change.
