Oftentimes, we have blank values in our dataset. If there is no value in the first match and the exact match condition is set, the VLOOKUP function will return 0. In this article, we will show you 3 handy approaches to skip to the next result in VLOOKUP if blank.
How to Skip to Next Result with VLOOKUP If Blank Cell Is Found: 3 Handy Approaches
The first two methods show how to modify the VLOOKUP function so that it always returns the next result if any rows are empty. Afterward, the last method will show you the steps to return non blank values from the next column.
Moreover, to demonstrate the methods, we have picked this dataset containing 2 columns: “Name” and “Sales”. We can see there are two “Rowan” in the “Name” column, but the first one has no value in the “Sales” column. Therefore, we have returned the next non blank value. We will do this using the three methods.
1. Combining VLOOKUP and IFNA Functions
In this section, we will combine the VLOOKUP and IFNA functions to skip to the next result if blanks are found. Additionally, we will set the range_lookup to the approximate match. Remember that if there are more than two values, this formula will return the last one. It will not check if the second value is blank or not. So, if you have more than two values with blanks, use the second method.
Steps:
- To begin with, type “Ben” into the cell B13.
- Then, type the following formula in cell C13.
=IFNA(VLOOKUP(B13,B5:C10,2,1),VLOOKUP(B13,B5:C10,2,0))
- After that, press ENTER.
- Thus, we can skip to the next value in VLOOKUP if blank.
Formula Breakdown
- There are two VLOOKUP functions inside the IFNA function.
- VLOOKUP(B13,B5:C10,2,1)
- Output: 6120.
- This function matches the value from cell B13 with the B5:C10 range. Then it returns the value from the second column. Lastly, we have set the range_lookup as an approximate match by typing 1.
- VLOOKUP(B13,B5:C10,2,0)
- Output: 0.
- This function matches the value from cell B13 with the B5:C10 range. Then it returns the value from the second column. Lastly, we have set the range_lookup as an exact match by typing 0.
- The formula reduces to → IFNA(6120,0)
- Output: 6120.
- If “#N/A” was returned by the first VLOOKUP function, then we would have gotten the second value from this function. Which we will see if we change cell B13‘s value to Alan.
Read More: How to Skip Every Other Column Using Excel Formula
2. Incorporating IFS and VLOOKUP Functions to Skip to Next Result
We will incorporate the IFS and VLOOKUP functions to create a formula to skip to the next result. Moreover, we have slightly altered the dataset. Now, we have three same names inside the first column. The first method will return the last value, but we need the second value. To do this, follow the step by step guide.
Steps:
- Firstly, type the following formula in cell C13.
=IFS(VLOOKUP(B13,B5:C10,2,0)<>0,VLOOKUP(B13,B5:C10,2,0),VLOOKUP(B13,B6:C10,2,0)<>0,VLOOKUP(B13,B6:C10,2,0),VLOOKUP(B13,B7:C10,2,0)<>0,VLOOKUP(B13,B7:C10,2,0),VLOOKUP(B13,B8:C10,2,0)<>0,VLOOKUP(B13,B8:C10,2,0),VLOOKUP(B13,B9:C10,2,0)<>0,VLOOKUP(B13,B9:C10,2,0),VLOOKUP(B13,B10:C10,2,0)<>0,VLOOKUP(B13,B10:C10,2,0))
- Then, press ENTER.
- So, it will skip the blank value and return the next value.
Formula Breakdown
- First of all, do not get intimidated by the size of the formula. We have used the same thing repeatedly in this formula.
- Then, the IFS function allows us to type simpler formulas than using the nested IF statement.
- VLOOKUP(B13,B5:C10,2,0)<>0
- Output: FALSE.
- This logical_test checks if the VLOOKUP function returns anything other than zero. If it is not zero then we will get this value.
- Then, this formula checks the same thing with a smaller cell range, B6:C10 then B7:C10 and so on.
- Then, the formula reduces to → IFS(FALSE,0,FALSE,0,FALSE,0,FALSE,0,TRUE,2560,#N/A,#N/A)
- Output: 2560.
- There is only one true value and thus we have got the value.
- Additionally, we can check if the formula is working perfectly by changing the values in cell B13.
Read More: How to Skip Columns in Excel Formula
3. Skip to Next Result by Merging IF and VLOOKUP Functions
For the last method, we will merge the IF and VLOOKUP functions to create another formula to skip to the next column if blank. This is the most convenient way to achieve our objective among the three methods. Therefore, you can change your dataset to this format if you want to return the next non blank.
Steps:
- Firstly, type the following formula in cell C13.
=IF(VLOOKUP(B13,B5:D10,2,0)="",VLOOKUP(B13,B5:D10,3,0),VLOOKUP(B13,B5:D10,2,0))
- Then, press ENTER.
- This formula will move to the next column, if the previous column is blank.
Read More: How to Skip to Next Cell If a Cell Is Blank in Excel
Practice Section
We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.
Download Practice Workbook
Conclusion
We have shown you three quick ways to skip to the next result in VLOOKUP if blank. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thanks for reading, keep excelling!
Related Articles
- Excel Formula to Skip Rows Based on Value
- How to Skip Cells When Dragging in Excel
- Skip a Column When Selecting in Excel
- How to Skip Cells in Excel Formula
- How to Skip Lines in Excel
- How to Skip Blank Rows Using Formula in Excel
- How to Skip Hidden Cells When Pasting in Excel
<< Go Back to Skip Cells | Excel Cells | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
On option 1, if the column for Ben was inserted with Rowan then Ben again, what would be the formula?
Original Formula:
=IFNA(VLOOKUP(B13,B5:C10,2,1),VLOOKUP(B13,B5:C10,2,0))
Hello Zeus, thanks for reaching out. If you want the sales data for Rowan and Ben in a single column, you simply insert the names Ben and Rowan in the new Name column (Cells B13 and B14). Putting the original formula in cell C13 will return the sales data about Rowan. Then you just drag the Fill icon downwards to copy the formula in C14 which will show the data of Ben. Putting names multiple times in the output part is not necessary. This will incident redundant result.