How to Use an Excel Lookup Formula with Wildcards and Two Criteria in WPS Office
Question details
The user needs a lookup formula in WPS Office Spreadsheets that returns a sales code by matching a customer, a product, or both, while treating blank cells in the lookup table as wildcards.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A sales lookup table contains customer rules in column A, product rules in column B, and sales codes in column C. The user enters criteria and wants the first matching sales code, with blank rule cells matching any value.
- Observed behavior
- The goal state is a formula that returns the first matching sales code and falls back to the product code when no matching rule is found.
Open your WPS Spreadsheets workbook and confirm the lookup range is A2:C7 with Customer in column A, Product in column B, and Sales Code in column C. Enter the criteria cells exactly as referenced in the formula: customer in A10 and product in C10; if your regional version uses semicolon separators, replace the commas in the formula accordingly.
Use an INDEX/MATCH Array Formula That Treats Blank Table Cells as Wildcards
Use this single-cell formula when you want to match both criteria, let blank cells in columns A or B act as wildcards, and return a fallback value without adding helper columns.
WPS Office Spreadsheets can evaluate Excel-style array formulas. The expression ($A$2:$A$7="")+($A$2:$A$7=$A$10) returns a nonzero condition when a Customer rule is blank or equals the customer in A10; the Product condition uses the same pattern for column B and C10.
Click the cell where the sales code should appear, for example D10.
In the Formula Bar, type =IFERROR(INDEX($C$2:$C$7,MATCH(1,IF(($A$2:$A$7="")+($A$2:$A$7=$A$10),1,0)*IF(($B$2:$B$7="")+($B$2:$B$7=$C$10),1,0),0)),$C$10).
Press Ctrl+Shift+Enter if your WPS Spreadsheets version requires array entry. The result should show a sales code instead of an error.
Change the customer in A10 and product in C10; verify rows with blank cells in columns A or B still match.
Use a Helper Column to Avoid Array Formula Entry
Use this approach if you prefer a regular formula that is easier to edit and does not require Ctrl+Shift+Enter.
Build a Flexible Two-Criteria Lookup with WPS Office Spreadsheets
Use WPS Spreadsheets to open your Excel workbook, enter an INDEX/MATCH formula that treats blank lookup cells as wildcards, and return the first matching sales code or a fallback product code. WPS Office keeps your formulas compatible with Microsoft Excel .xlsx files.
- 1. Download WPS Office Free: Open https://www.wps.com, click Download WPS Office Free, and install the latest WPS Office version.
- 2. Open the Excel lookup workbook: Launch WPS Spreadsheets, click Menu > Open, and select the .xlsx workbook that contains the customer/product sales table.
- 3. Apply the recommended formula: Select the result cell, enter the INDEX/MATCH array formula from the solution above, and press Ctrl+Shift+Enter if needed.
- 4. Verify the wildcard fallback: Test with an exact match, a blank customer rule, a blank product rule, and a no-match case to confirm the formula returns the expected sales code or product fallback.
- 5. Save for Microsoft Office compatibility: Click Menu > Save As and keep the Excel .xlsx format so the workbook remains compatible with Microsoft Excel.
Frequently Asked Questions
Can I use this wildcard lookup formula in WPS Office if the workbook was created in Excel?
Yes. Open the .xlsx workbook in WPS Spreadsheets and use the INDEX/MATCH array formula. If your WPS version requires legacy array entry, press Ctrl+Shift+Enter instead of Enter.
What does it mean when a blank cell in columns A or B is treated as a wildcard?
It means the row condition ignores that criterion. For example, a blank Product rule matches any product for the Customer value in the same row, so the formula can use a short table of exception rules.
Why does the formula return the product code instead of #N/A when no row matches?
The IFERROR function catches the failed MATCH and returns the final argument, which is the product code in C10. Change that final argument to another fallback value if required.
Do I need to press Ctrl+Shift+Enter for every cell with this formula?
If you use the single-cell array formula, enter it once as an array formula and then edit it with Ctrl+Shift+Enter. If you use the helper-column solution, no array shortcut is needed.
Can I extend the formula to match three or more criteria?
Yes. Add another condition array for each extra column using the same pattern, such as *IF(($D$2:$D$7="")+($D$2:$D$7=$D$10),1,0), and keep the lookup range consistent.
