WPS Spreadsheet: Look Up Product Prices Without Nested IF Formulas
Question details
The user needs to retrieve a product price by entering a model number without creating a large nested IF formula.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Model numbers are stored in A2:A301, matching prices are in B2:B301, and the searched model number is entered in C2.
- Observed behavior
- The desired result is for WPS Spreadsheet to return the matching price or display “Not found” when the model number does not exist, without relying on nested IF formulas.
Confirm that each model number in A2:A301 matches the correct price in B2:B301, and check that the lookup value in C2 uses the same text or number format as the model-number list.
Use XLOOKUP to return the product price by model number
Use this method when your WPS Spreadsheet version supports XLOOKUP and you want a simple exact-match formula.
XLOOKUP searches the model-number column and returns the corresponding value from the price column. IFERROR replaces a missing match with a clear message instead of an error code.
In WPS Spreadsheet, click the cell where the price should appear, such as D2.
Type =IFERROR(XLOOKUP(C2,A2:A301,B2:B301),"Not found") and press Enter.
Select the formula cell and drag its fill handle down column D, or copy and paste it into the remaining result cells.
Use INDEX and MATCH for older WPS Spreadsheet versions
Use this compatible alternative when XLOOKUP is unavailable or when you need a traditional exact-match lookup formula.
Find Product Prices Quickly with WPS Spreadsheet Lookup Formulas
WPS Spreadsheet lets you match model numbers to prices with XLOOKUP or INDEX MATCH, making large product lists easier to maintain than nested IF formulas.
- 1. Open the product list: Launch WPS Office, open WPS Spreadsheet, and open the workbook containing the model numbers and prices.
- 2. Enter the lookup formula: Click the result cell and enter =IFERROR(XLOOKUP(C2,A2:A301,B2:B301),"Not found").
- 3. Use the compatibility alternative if needed: If XLOOKUP is unavailable, enter =IFERROR(INDEX($B$2:$B$301,MATCH(C2,$A$2:$A$301,0)),"Not found") instead.
- 4. Apply the formula to the list: Copy the completed formula down the result column to return a price for each model number.
Frequently Asked Questions
What formula looks up a product price by model number in WPS Spreadsheet?
If model numbers are in A2:A301, prices are in B2:B301, and the searched model is in C2, enter =IFERROR(XLOOKUP(C2,A2:A301,B2:B301),"Not found") in the result cell.
What should I use if XLOOKUP is not available?
Use =IFERROR(INDEX($B$2:$B$301,MATCH(C2,$A$2:$A$301,0)),"Not found"). This INDEX MATCH formula performs an exact lookup and works in older spreadsheet versions.
How do I show prices automatically for multiple model numbers?
Enter the lookup formula in the first result cell, such as D2, then copy or drag it down the column. Each row will use the model number from its corresponding cell in column C.
Why does the formula return “Not found” for an existing model number?
Check for extra spaces, inconsistent text and number formats, or different capitalization and characters. Make sure the searched model and the values in A2:A301 are stored in compatible formats.
Can I replace the “Not found” message with a blank cell?
Yes. Replace "Not found" in the IFERROR formula with an empty string: =IFERROR(XLOOKUP(C2,A2:A301,B2:B301),"").
