Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Skip to Next Result with VLOOKUP If Blank Cell Is Present

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.


Download Practice Workbook


3 Handy Approaches to Skip to Next Result with VLOOKUP If Blank Cell Is Found

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 Blank Rows Using Formula in Excel (8 Methods)


Similar Readings


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


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 (5 Easy Ways)


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


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. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo