How to Make Excel VLOOKUP Return Found, Not Found, or Blank
Question details
The user needs to modify a standard VLOOKUP formula so that it outputs custom status labels like "Found" or "Not Found" instead of the actual matched value or error, and remains blank if the search cell is empty.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Searching data lists where exact matched cell values are less important than knowing the existence of a record, requiring a clean presentation without standard Excel error codes.
- Observed behavior
- A standard VLOOKUP returns the target cell's exact value or an #N/A error. The goal state is to output conditional text based on whether a match exists or the input is empty.
Ensure your lookup range is properly defined with absolute references (like $A$1:$D$100) and that the data format of your search key strictly matches the data format in the first column of your lookup table.
Use a Nested IF, IFERROR, and VLOOKUP Formula
Combine the VLOOKUP function with IF and IFERROR to check for blank input cells, evaluate the lookup result, and replace standard #N/A errors with custom text.
By nesting functions, you can control exactly what Excel outputs under different conditions. The outer IF function checks if the search cell is blank. The inner VLOOKUP performs the search, while the IFERROR catches any non-matches to display "Not Found".
Click on the cell where you want the "Found", "Not Found", or blank result to appear.
Type the formula: =IF(A1="","",IFERROR(IF(VLOOKUP(A1,lookup_range,return_column,FALSE)<>"","Found"),"Not Found")). Replace 'A1' with your search cell, 'lookup_range' with your data table, and 'return_column' with the column index number.
Press the Enter key to execute the formula.
Click the small square at the bottom-right corner of the selected cell and drag it down to apply this customized lookup logic to the rest of your column.
Master Lookup Functions Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced nested formulas like IF, IFERROR, and VLOOKUP, making it incredibly simple to analyze, categorize, and clean your data efficiently without complex workarounds.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
- 2. Input the formula: Click on the target cell and begin typing your nested VLOOKUP formula just as you would in Excel.
- 3. Utilize formula hints: Take advantage of the intelligent auto-complete and syntax tooltips provided by WPS to accurately select your lookup ranges.
- 4. Apply and drag: Press Enter to see the result, then double-click the fill handle to populate the entire column instantly.
Frequently Asked Questions
Why does my VLOOKUP return #N/A instead of "Not Found"?
If you don't wrap your VLOOKUP function in an IFERROR or IFNA function, Excel will naturally return an #N/A error when an exact match isn't found in the designated lookup range.
Can I use XLOOKUP to achieve this result instead of VLOOKUP?
Yes, XLOOKUP is more modern and has a built-in "if_not_found" argument. You can use a formula like =IF(A1="","",IF(XLOOKUP(A1, lookup_array, return_array, "Not Found")<>"Not Found","Found","Not Found")) to achieve the same result with slightly different logic.
What if my lookup value is a number but stored as text?
If the data types do not match exactly, the VLOOKUP will fail to find a match and will return "Not Found". Ensure both the lookup value cell and the first column of your search range are formatted the exact same way (either both as text or both as numbers).
How do I make the formula return the actual value if it's found, but "Not Found" if it isn't?
You can simplify the formula to just handle the errors and blanks. Use: =IF(A1="","",IFERROR(VLOOKUP(A1,lookup_range,return_column,FALSE),"Not Found")). This will return the actual matched value instead of the word "Found".
