How to Fix IFERROR and VLOOKUP Returning Blank Results in Excel
Question details
The user's formula combining IFERROR and VLOOKUP is returning an unexpected blank result instead of the target data.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Using VLOOKUP nested inside an IFERROR function to retrieve data from another sheet or table range without displaying default error codes.
- Observed behavior
- The formula returns a blank cell because the requested column index exceeds the specified lookup range (e.g., requesting column 19 from a 17-column range), triggering a #REF! error that the IFERROR function suppresses and replaces with a blank.
Check your formula carefully to ensure that the column index number (col_index_num) in your VLOOKUP function is not greater than the total number of columns in your selected table array.
Expand the VLOOKUP Table Array Range
Adjust the lookup range in your formula to include all necessary columns, preventing the #REF! error that IFERROR hides.
VLOOKUP counts columns from left to right starting at the beginning of your selected range. If your range is B:R (17 columns) and you ask for column 19, the function fails. Expanding the range solves this immediately.
Click on the cell that contains your problematic IFERROR and VLOOKUP formula to view it in the formula bar at the top.
Identify the 'table_array' argument in your VLOOKUP formula, such as 'Calendar Control'!$B:$R.
Look at the 'col_index_num' argument (the number right after the table array). If this number is larger than the total columns in your array, you must expand the array.
Change the ending column letter in the table array to encompass the required columns (for example, update $B:$R to $B:$Z) and press Enter.
Replace VLOOKUP with XLOOKUP
Use the modern XLOOKUP function, which selects return arrays directly instead of relying on column index numbers, preventing out-of-bounds errors.
Solve Formula Errors Easily with WPS Spreadsheet
WPS Office provides a powerful Spreadsheet application fully compatible with Excel formulas, including VLOOKUP, XLOOKUP, and IFERROR. With advanced formula auditing tools, finding and fixing hidden errors is quick and straightforward.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the formula errors.
- 2. Use Error Checking tools: Navigate to the 'Formulas' tab on the top ribbon and click on 'Error Checking' to help identify underlying issues.
- 3. Evaluate the formula: Click 'Evaluate Formula' to step through your IFERROR and VLOOKUP calculation one step at a time, revealing exactly where it fails.
- 4. Correct the lookup range: Click into the formula bar and adjust your table array range directly to include the missing columns.
Frequently Asked Questions
Why does VLOOKUP return a blank when there is matching data?
If your VLOOKUP is wrapped in an IFERROR function set to return a blank (""), any underlying error—such as an invalid column index (#REF!) or a missing match (#N/A)—will be completely hidden and displayed as a blank cell.
How do I find the correct column index number for VLOOKUP?
The column index number is the position of the column within your selected lookup range, starting with 1 for the leftmost column. For example, if your table array is C:F, column C is 1, D is 2, E is 3, and F is 4.
Can I make VLOOKUP automatically adjust if I insert new columns in my data?
Yes. Instead of typing a hardcoded number like '19', you can use the MATCH function to dynamically find the correct column index. Alternatively, switching to XLOOKUP or an INDEX/MATCH combination prevents column insertion errors entirely.
