WPS Office Spreadsheets VLOOKUP Formula to Display NOT FOUND for Missing Articles
Question details
The user needs to modify a VLOOKUP formula in WPS Office Spreadsheets so missing article numbers display the text NOT FOUND instead of an error or an approximate match.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is looking up article numbers from an Articles sheet and wants clear feedback when a lookup value is absent.
- Observed behavior
- A regular VLOOKUP returns #N/A for missing article numbers or can return an incorrect result if the formula does not force exact matching.
Make sure the Articles sheet contains the article numbers in the first column of the lookup range and that the lookup values in column A do not have leading or trailing spaces; exact-match VLOOKUP requires consistent text or number values.
Wrap an Exact-Match VLOOKUP in IFERROR to Display NOT FOUND
Use this solution when you want missing article numbers to return the text NOT FOUND and prevent VLOOKUP from accepting an approximate match.
Setting the fourth VLOOKUP argument to FALSE forces exact matching, so WPS Office Spreadsheets no longer returns a close but incorrect article. IFERROR then catches the resulting #N/A error and replaces it with NOT FOUND.
Open your workbook in WPS Office Spreadsheets and click the cell where you want the article lookup result to appear.
Type =IFERROR(VLOOKUP($A8,Articles!$A$1:$F$999,3,FALSE),"NOT FOUND") and press Enter.
Drag the fill handle down or copy the formula to the other rows that need to check article numbers from column A.
Use IFNA to Return NOT FOUND Only for Missing Lookup Values
Choose this if you want to catch only the #N/A error produced by a missing match and still see other formula errors for troubleshooting.
Display NOT FOUND for Missing Articles with WPS Office Spreadsheets
Open the workbook in WPS Office Spreadsheets and use the IFERROR and exact-match VLOOKUP combination to show a clean NOT FOUND message for missing article numbers. WPS Office supports the same formula syntax and Excel file formats, so you can apply this fix without changing tools.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the Excel file that contains the lookup formula.
- 2. Select the target cell: Click the cell where the formula result should appear, normally the first result cell in the column.
- 3. Enter the IFERROR VLOOKUP formula: Type =IFERROR(VLOOKUP($A8,Articles!$A$1:$F$999,3,FALSE),"NOT FOUND") and press Enter.
- 4. Copy the formula down the column: Use the fill handle to apply the formula to the remaining article lookup rows.
- 5. Confirm missing articles display NOT FOUND: Check rows with article numbers that are not in the Articles sheet; they should now display NOT FOUND without #N/A errors.
Frequently Asked Questions
Why does my VLOOKUP return #N/A when an article number is missing?
In WPS Office Spreadsheets, an exact-match VLOOKUP uses FALSE as the fourth argument and returns #N/A when the lookup value is not found. Wrapping the formula in IFERROR or IFNA lets you replace that error with NOT FOUND.
What does the FALSE argument do in the VLOOKUP formula?
The FALSE argument forces exact matching. Without it, VLOOKUP may use approximate matching and return a close value instead of the correct article, which is why it should be included for missing-article checks.
Can I use IFNA instead of IFERROR for this formula?
Yes. Use =IFNA(VLOOKUP($A8,Articles!$A$1:$F$999,3,FALSE),"NOT FOUND") to only replace #N/A errors. IFERROR would also hide other errors such as #VALUE! or #REF!.
Will this VLOOKUP NOT FOUND formula work in Microsoft Excel files?
Yes. WPS Office Spreadsheets supports the same VLOOKUP, IFERROR, and IFNA functions and is compatible with Microsoft Excel .xlsx files, so the formula behaves the same way when the file is opened in WPS Office.
How do I type NOT FOUND so it appears as text in the formula?
Place NOT FOUND inside double quotes in the formula, for example: =IFERROR(VLOOKUP($A8,Articles!$A$1:$F$999,3,FALSE),"NOT FOUND"). WPS Office Spreadsheets then treats it as text to display when the lookup fails.
