How to Use an Excel Formula to Find a Rate Using Multiple Conditions in WPS Spreadsheets
Question details
The user needs a formula to look up a rate from a rate card when the scheme code matches and the transaction value is between the lower and upper limits.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Looking up pricing or rate card data in a worksheet with scheme, lower limit, upper limit, and rate columns.
- Observed behavior
- The user wants the formula to return the matching rate value without manually scanning the rate card.
Make sure your rate card is structured with Scheme, Lower Limit, Upper Limit, and Rate in separate columns. Confirm the cell references for the lookup scheme and transaction value before entering the formula.
Use INDEX and MATCH with Multiple Conditions to Return the Rate
Use this formula when you need to match a scheme and a transaction value between lower and upper limits in WPS Spreadsheets or Excel.
This array formula multiplies the condition tests together to produce 1 only where all conditions are true, then MATCH finds that position and INDEX returns the corresponding rate. In older Excel versions, array formulas must be confirmed with Ctrl+Shift+Enter; WPS Spreadsheets and Microsoft 365 may handle dynamic arrays automatically.
Click the cell where you want to show the matching rate.
Type =INDEX($I$4:$I$12,MATCH(1,($F$4:$F$12=A3)*($G$4:$G$12<B3)*($H$4:$H$12>B3),0)) in the formula bar, replacing the ranges and cell references with your own rate card layout.
If your version of Excel or WPS Spreadsheets does not support dynamic arrays, press Ctrl+Shift+Enter instead of Enter to confirm the formula.
Check that the result is the expected rate for the matched scheme and transaction value.
Use SUMPRODUCT to Return a Matching Rate Without Array Entry
Use this alternative if you prefer a non-array formula and expect only one matching row in the rate card.
Find a Rate with Multiple Conditions in WPS Spreadsheets
Use WPS Spreadsheets to create the same INDEX/MATCH formula that returns a rate from a rate card based on scheme and value boundaries. It supports Excel file formats and familiar formula behavior, so you can complete the task without paying for Microsoft Excel.
- 1. Open WPS Spreadsheets: Launch WPS Office and open your existing .xlsx workbook or create a new spreadsheet.
- 2. Set up the rate card and lookup cells: Arrange the Scheme, Lower Limit, Upper Limit, and Rate columns, and enter the lookup values such as the scheme code and transaction value.
- 3. Enter the lookup formula: In the result cell, type the INDEX/MATCH formula that matches the scheme and checks the lower and upper limits.
- 4. Confirm and adjust the formula: Press Enter or Ctrl+Shift+Enter if your version requires array entry, then drag or copy the formula as needed.
- 5. Save your workbook: Save the file as .xlsx or the WPS format to keep the formula results and compatibility.
Frequently Asked Questions
Does this formula work in older Excel versions?
Yes. Use the INDEX/MATCH formula and press Ctrl+Shift+Enter in older Excel versions that do not support dynamic arrays.
Why is my formula returning #N/A or 0?
Check that the scheme text matches exactly, the transaction value is greater than the lower limit and less than the upper limit, and the ranges use absolute references where needed.
Can I use XLOOKUP instead of INDEX/MATCH for multiple conditions?
Yes, if your version supports XLOOKUP, you can use Boolean arrays like =XLOOKUP(1,($F$4:$F$12=A3)*($G$4:$G$12<B3)*($H$4:$H$12>B3),$I$4:$I$12).
Do I need to use dollar signs in the formula?
Dollar signs lock the rate card range so the formula can be filled down without shifting the lookup range.
