How to Match Two Criteria and Return an Invoice Number in Excel
Question details
The user needs a formula to look up and return an invoice number based on matching two specific conditions simultaneously, such as a customer name and a completion status.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Looking up and retrieving specific invoice numbers from a dataset when two distinct corresponding criteria are met.
- Observed behavior
- The goal state is to extract matching invoice numbers using an efficient formula, overcoming 'invalid function' errors in older Excel versions.
Ensure your dataset is organized with clear column headers and no merged cells. Identify the exact columns containing your criteria and the column containing the invoice numbers before writing the formula.
Use the FILTER Function in Microsoft 365
The FILTER function is the most efficient dynamic array method to handle multiple criteria and return matching values.
The FILTER function allows you to extract data based on criteria you define. By multiplying two conditions together using an asterisk, you can create a logical 'AND' statement, ensuring both criteria are met before returning the invoice number.
Click on the empty cell where you want the matching invoice number to appear on your worksheet.
Type =FILTER( to begin the formula, then highlight the range containing your invoice numbers (e.g., A2:A10).
Type a comma to proceed to the 'include' argument. Open a parenthesis, highlight your first criteria range (e.g., B2:B10), type an equals sign, and select your criteria target cell (e.g., F2). Close the parenthesis.
Type an asterisk to represent 'AND'. Open another parenthesis, highlight your second criteria range (e.g., C2:C10), type an equals sign, and select your second target cell (e.g., G2). Close the parenthesis.
Close the final parenthesis so your formula looks like =FILTER(A2:A10,(B2:B10=F2)*(C2:C10=G2)) and press Enter. The matching invoice numbers will populate instantly.
Use INDEX and MATCH Array Formula for Older Excel Versions
If you do not have Microsoft 365, you can use a combination of INDEX and MATCH to handle multiple conditions.
Easily Match Multiple Criteria in WPS Spreadsheet
WPS Office provides a highly compatible spreadsheet application where you can easily use advanced lookup formulas, including FILTER and INDEX/MATCH, to quickly retrieve data based on multiple conditions.
- 1. Open Your Workbook: Launch WPS Office and open your dataset containing the invoice numbers in WPS Spreadsheet.
- 2. Select the Result Cell: Click on the specific cell where you want the matched invoice number to be returned.
- 3. Input the Lookup Formula: Type your preferred multiple-criteria formula, such as =FILTER(A2:A10,(B2:B10=F2)*(C2:C10=G2)).
- 4. Press Enter to Retrieve Data: Press Enter on your keyboard to instantly calculate and view the matched results.
Frequently Asked Questions
Why am I getting a 'That function isn't valid' error when using FILTER?
This error typically occurs because the FILTER function is only supported in Microsoft 365, Excel 2021, and newer versions. For older versions of Excel, you must use an alternative method like the INDEX and MATCH array formula.
How do I return multiple invoice numbers that match the same criteria?
The FILTER function is designed as a dynamic array. If multiple rows in your dataset match both of your criteria, the function will automatically 'spill' the results into the adjacent cells downwards, returning all matching invoice numbers at once.
Can I use 'OR' logic instead of 'AND' logic in the FILTER function?
Yes. To use 'OR' logic (where either the first OR the second condition is met), replace the asterisk (*) with a plus sign (+) between the criteria arrays in your formula.
