How to Create an Excel Lookup Formula for Multiple Criteria and Ranges
Question details
The user needs an Excel formula to retrieve a specific price based on three criteria: manufacturer, a quantity falling within a min/max range, and the selected payment method.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating dynamic pricing from a structured data table using multiple lookup conditions.
- Observed behavior
- The user wants to return the correct pricing value from either a Cash or SAC column when the manufacturer matches and the quantity is between defined minimum and maximum bounds.
Ensure your pricing data is organized into clear, separate columns for Manufacturer, Panel Min, Panel Max, Cash Price, and SAC Price without any merged cells.
Use an INDEX and MATCH Array Formula
This method uses boolean logic within the MATCH function to evaluate multiple conditions, including number ranges, and dynamically selects the return column based on the payment method.
By multiplying conditional statements together, Excel creates an array of 1s and 0s. The MATCH function then looks for the first row where all conditions evaluate to true (1).
Organize your data in a range (e.g., A2:E7) with columns for Manufacturer, Panel Min, Panel Max, Cash Price, and SAC Price.
Designate specific cells for your lookup criteria. For instance, use G2 for the target Manufacturer, G3 for the Quantity, and G4 for the Payment Method (Cash or SAC).
Select the cell where you want the final price to appear and type the following formula: =INDEX(IF(G4="Cash",D2:D7,E2:E7),MATCH(1,(A2:A7=G2)*(G3>=B2:B7)*(G3<=C2:C7),0))
If you are using Microsoft 365 or modern WPS Office, press Enter. If you are using an older version of Excel, you must press Ctrl+Shift+Enter to evaluate it properly as an array formula.
Use the FILTER Function (Modern Excel/WPS)
For newer versions of Excel or WPS Office, the FILTER function provides a cleaner and more scalable way to extract data based on multiple criteria without needing complex array keystrokes.
Handle Complex Lookups Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, XLOOKUP, and dynamic array functions like FILTER, making it incredibly easy to build complex pricing models and multi-criteria lookups.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your pricing tables.
- 2. Select the target cell: Click on the cell where you want your dynamic price calculation to appear.
- 3. Enter your lookup formula: Type in your INDEX/MATCH, FILTER, or XLOOKUP formula exactly as you would in Microsoft Excel.
- 4. Make your selections: Change your criteria inputs (like Manufacturer or Quantity) and press Enter to see the calculations update instantly.
Frequently Asked Questions
Why does my INDEX MATCH formula return a #VALUE! or #N/A error?
A #VALUE! error usually occurs in older Excel versions if you forget to press Ctrl+Shift+Enter to confirm an array formula. An #N/A error means no row in your data matched all the specified criteria (e.g., the inputted quantity falls outside all defined min/max ranges or the manufacturer is misspelled).
Can I use XLOOKUP for multiple criteria with number ranges?
Yes, you can use XLOOKUP by utilizing boolean logic in the lookup array. For example: =XLOOKUP(1, (A2:A7=G2)*(B2:B7<=G3)*(C2:C7>=G3), IF(G4="Cash", D2:D7, E2:E7)). This searches for the value '1' (True) across the combined condition arrays.
How do I handle missing data or prevent errors when no exact match is found?
If using the FILTER or XLOOKUP functions, you can utilize their built-in 'if_not_found' arguments to display a custom message. For older INDEX/MATCH formulas, wrap the entire formula in an IFERROR function, such as: =IFERROR(your_index_match_formula, "Price not found").
