Skip to Next Result with VLOOKUP If Blank Cell Is Present

Get FREE Advanced Excel Exercises with Solutions!

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.

VLOOKUP If Blank Skip to Next Result


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))

Combining VLOOKUP and IFNA Functions

  • After that, press ENTER.
  • Thus, we can skip to the next value in VLOOKUP if blank.

VLOOKUP If Blank Skip to Next Result

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))

Incorporating IFS and VLOOKUP Functions to Skip to Next Result

  • 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))

Skip to Next Result If Blank by Merging IF and VLOOKUP Functions

  • 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.

Practice Dataset


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


<< Go Back to Skip Cells  | Excel Cells | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

2 Comments
  1. 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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo