How to Perform a Two-Criteria Price Lookup by Rate and Job Number in Excel
Question details
The user needs to retrieve an invoice price from a separate price list based on two distinct conditions: the selected rate type (e.g., day or night) and the specific job number.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up an automated invoice or billing sheet where the correct price must be dynamically fetched by matching both the job number and the time rate from a master price sheet.
- Observed behavior
- To accurately extract the corresponding price from the data range without returning standard Excel errors when a match is not found.
Ensure that your master price list table is properly structured with separate columns for Rate, Job Number, and Price, and verify that both source ranges have the exact same number of rows.
Use INDEX and MATCH Array Formula
Combining INDEX, MATCH, and boolean logic is the most robust method for performing multi-criteria lookups in Excel.
This formula uses boolean logic within the MATCH function to evaluate multiple conditions simultaneously. By multiplying the two criteria arrays together, it creates an array of 1s and 0s. When both conditions are met (TRUE * TRUE), the result is 1. The MATCH function then searches for the number 1 to return the correct row number to the INDEX function.
Click on the cell in your invoice where you want the calculated price to appear.
Type the formula: =IFNA(INDEX('Price Sheet'!$C$2:$C$21,MATCH(1,($G$3='Price Sheet'!$A$2:$A$21)*(B9='Price Sheet'!$B$2:$B$21),0)),""). Be sure to replace the cell references with your actual ranges. In this example, $G$3 is the rate criterion, B9 is the job number criterion, and column C contains the target prices.
If you are using an older version of Excel, you must press Ctrl+Shift+Enter to convert it into an array formula (curly braces will appear around it). In Microsoft 365 or newer versions, simply press Enter.
Perform Complex Data Lookups Effortlessly with WPS Spreadsheet
WPS Office Spreadsheet fully supports array formulas and advanced lookup functions like INDEX, MATCH, XLOOKUP, and IFNA. You can execute multiple-criteria lookups exactly as you would in Microsoft Excel, completely for free.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your invoice and price list files.
- 2. Input the multi-criteria formula: Select your destination cell and type the exact same INDEX and MATCH formula combining your Rate and Job Number criteria.
- 3. Calculate the result: Press Enter to execute the function. WPS Spreadsheet instantly processes the array logic and returns your correct invoice price.
Frequently Asked Questions
Can I use XLOOKUP for two criteria instead of INDEX and MATCH?
Yes, if you have a newer version of Excel or use WPS Spreadsheet, you can use XLOOKUP with multiple criteria using boolean logic. The syntax looks like this: =XLOOKUP(1, (Criteria1Range=Criteria1) * (Criteria2Range=Criteria2), ReturnRange, ""). Many users find this more straightforward than the INDEX and MATCH combination.
Why is my formula returning a #VALUE! error?
A #VALUE! error typically occurs if the ranges for your criteria are not exactly the same size. Ensure that the ranges for the Rate column (e.g., A2:A21), Job Number column (B2:B21), and Price column (C2:C21) all have the exact same number of rows and starting/ending points.
Why do I need to press Ctrl+Shift+Enter in older versions?
Multiplying two arrays (like matching both the rate array and the job number array) creates an array operation. Older versions of Excel require the Ctrl+Shift+Enter keystroke to explicitly tell the program to evaluate the formula as an array. Modern spreadsheet programs, including newer Excel versions and WPS Office, handle these dynamic arrays automatically.
