WPS Spreadsheet: Use Two Conditions in an Excel Lookup Formula
Question details
The user needs a lookup formula that returns a product label based on both the customer or city in A1 and the product in B1.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A pricing or labeling table contains products in rows and customer or city headings in columns, and the result must match both input conditions.
- Observed behavior
- The desired result depends on the intersection of the product row and the customer or city column rather than on a single lookup value.
Arrange the lookup table with product names in the first column, such as I2:I7, and customer or city headings across the top row, such as J1:K1. Confirm that the input cells A1 and B1 exactly match a heading and a product name in the table.
Use INDEX and MATCH for a two-condition lookup
Use this solution when the lookup value must match both a product and a customer or city heading.
INDEX returns the value from the result table, while the two MATCH functions identify the matching row and column. This structure is easier to expand and maintain than deeply nested IF functions.
In WPS Spreadsheet, place product names in I2:I7, customer or city headings in J1:K1, and the corresponding labels or results in J2:K7.
Click the cell where you want the label to appear, then open the Formula tab or enter a formula directly in the formula bar.
Enter =INDEX($J$2:$K$7,MATCH(B1,$I$2:$I$7,0),MATCH(A1,$J$1:$K$1,0)) and press Enter. The first MATCH uses B1 to find the product row, and the second MATCH uses A1 to find the customer or city column.
If your table has more products or customer columns, replace $I$2:$I$7, $J$1:$K$1, and $J$2:$K$7 with ranges that include all of your data.
Build two-condition lookup formulas in WPS Spreadsheet
WPS Spreadsheet can combine INDEX and MATCH to return the value at the intersection of a product row and a customer or city column. Enter the formula in a result cell, adjust the ranges to your table, and save the workbook in a familiar Excel-compatible format.
- 1. Open a spreadsheet: Launch WPS Office, choose Spreadsheet, and open your workbook or create a blank sheet.
- 2. Set up the lookup table: Place products in the first column, customer or city headings in the top row, and result labels in the table body.
- 3. Enter the formula: Select the output cell and enter =INDEX($J$2:$K$7,MATCH(B1,$I$2:$I$7,0),MATCH(A1,$J$1:$K$1,0)).
- 4. Update and verify the ranges: Expand the formula ranges when adding rows or columns, then test the formula with different values in A1 and B1.
Frequently Asked Questions
Which cell should contain the product value in this formula?
The product value should be in B1 because the first MATCH searches B1 in the product range, such as $I$2:$I$7.
Where does the customer or city condition go?
Place the customer or city value in A1. The second MATCH searches A1 across the heading range, such as $J$1:$K$1.
How do I add more customer or city columns?
Extend both the heading range and the result range. For example, change $J$1:$K$1 to $J$1:$M$1 and $J$2:$K$7 to $J$2:$M$7.
Why does the formula return an error?
Check that A1 exactly matches a column heading and B1 exactly matches a product name. Also confirm that the INDEX result range has the same number of rows and columns represented by the MATCH ranges.
Can I use this formula in an Excel XLSX file?
Yes. WPS Spreadsheet supports the INDEX and MATCH functions and can open and save Microsoft Excel XLSX workbooks.
