Check and Improve a Nested IF and VLOOKUP Formula in WPS Office Spreadsheets
Question details
The user needs to verify and improve a nested IF and VLOOKUP formula that selects different VLOOKUP columns based on a condition in another worksheet.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a formula that uses a nested IF to choose between VLOOKUP column indexes based on values in 'Post Frame Input' and wants reliable lookup results.
- Observed behavior
- The formula may return #N/A, #REF!, or the fallback value if sheet references, lookup ranges, column indexes, or exact-match settings are incorrect.
Open the workbook in WPS Office Spreadsheets and confirm that the sheet names 'Post Frame Input' and 'Truss prices' exactly match the workbook tabs. Make sure the lookup value in 'Post Frame Input'!C71 appears in the first column of 'Truss prices'!B9:H26.
Audit the nested IF and VLOOKUP references step by step
Use this full audit when the formula returns #N/A, #REF!, or the fallback value 1000000 unexpectedly.
The nested IF and VLOOKUP formula is structurally valid. A single mismatch in a sheet name, lookup range, column index, or data type is usually what makes the result unreliable, so verify each reference before changing the formula.
Select the cell that contains the formula in WPS Office Spreadsheets, then go to the Formulas tab and use Trace Precedents or the Name Box to inspect which cells feed the formula.
Confirm that sheets named 'Post Frame Input' and 'Truss prices' exist with exactly those names, including spaces and capitalization.
Select 'Truss prices'!B9:H26 and confirm that the lookup value in 'Post Frame Input'!C71 exists in the first column B9:B26; otherwise VLOOKUP with FALSE cannot find a match.
Ensure column 3 and column 6 are the intended price columns inside B9:H26, and verify that C408, D42, and D43 contain compatible values or text before comparing them.
After correcting the references, press Enter and change C408 to D42 or D43 to confirm that the formula returns the correct VLOOKUP column result.
Wrap the formula with IFERROR for clearer error handling
Use this when you want the formula to return a friendly message or a safe fallback instead of #N/A.
Use XLOOKUP or INDEX/MATCH as a clearer alternative
Use this when you want a more direct lookup that does not depend on counting columns inside a fixed range.
Check and Improve Nested IF and VLOOKUP Formulas in WPS Office Spreadsheets
WPS Office Spreadsheets lets you audit, correct, and improve nested IF and VLOOKUP formulas directly. Use the Formulas tab to trace references, verify lookup ranges, and switch to IFERROR or XLOOKUP for clearer, more reliable results.
- 1. Open the workbook: Download WPS Office, open your Excel workbook in WPS Spreadsheets, and select the cell containing the nested IF and VLOOKUP formula.
- 2. Audit formula references: Go to the Formulas tab and use Trace Precedents to see the input cells, lookup range, and sheet references used by the formula.
- 3. Correct the VLOOKUP parameters: Verify that the lookup value appears in the first column of 'Truss prices'!B9:H26 and that the column index points to the correct price column.
- 4. Improve the formula: Wrap the formula with IFERROR or replace VLOOKUP with XLOOKUP to make errors and return ranges easier to manage.
- 5. Test the result: Enter each comparison value in C408 and confirm the formula returns the correct VLOOKUP column result or fallback value.
Frequently Asked Questions
Why does my nested IF and VLOOKUP formula return #N/A even though the value looks present?
Usually the lookup value is not in the first column of B9:H26 or the value types do not match. In WPS Office Spreadsheets, check that C71 matches a value in B9:B26 and use FALSE for an exact match.
Can I use XLOOKUP instead of VLOOKUP in WPS Office Spreadsheets?
Yes. Recent versions of WPS Office Spreadsheets support XLOOKUP, so you can return a specific range directly instead of counting a column index inside B9:H26.
How do I make the formula return 1000000 only when neither D42 nor D43 matches C408?
Keep the final argument as 1000000 and make sure the comparisons are exact: =IF('Post Frame Input'!C408='Post Frame Input'!D42,VLOOKUP(...),IF('Post Frame Input'!C408='Post Frame Input'!D43,VLOOKUP(...),1000000)).
What should I check when I get #REF! after moving or renaming sheets?
Update the formula's sheet references if 'Post Frame Input' or 'Truss prices' was renamed, moved, or deleted, and confirm the referenced ranges B9:H26 and cell addresses still exist.
