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.

### 1. Combining VLOOKUP and IFNA Functions

In this section, we will combine the ** VLOOKUP **and

**functions to**

*IFNA***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
if blank.*VLOOKUP*

**Formula Breakdown**

- There are two
functions inside the*VLOOKUP*function.*IFNA* **VLOOKUP(B13,B5:C10,2,1)****Output: 6120**.- This function matches the value from cell
with the*B13*range. Then it returns the value from the second column. Lastly, we have set the*B5:C10**range_lookup*as an approximate match by typing**1**.

**VLOOKUP(B13,B5:C10,2,0)****Output: 0**.- This function matches the value from cell
with the*B13*range. Then it returns the value from the second column. Lastly, we have set the*B5:C10**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
function, then we would have gotten the second value from this function. Which we will see if we change cell*VLOOKUP*‘s value to Alan.*B13*

**Read More: ****How to Skip Blank Rows Using Formula in Excel (8 Methods)**

**Similar Readings**

**How to Skip Every Other Column Using Excel Formula (3 Methods)****Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows****How to Skip Hidden Cells When Pasting in Excel (2 Methods)**

### 2. Incorporating IFS and VLOOKUP Functions to Skip to Next Result

We will incorporate the ** IFS** and

**functions to create a formula to**

*VLOOKUP***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
function allows us to type simpler formulas than using*IFS*statement.*the nested IF* **VLOOKUP(B13,B5:C10,2,0)<>0****Output: FALSE**.- This
*logical_test*checks if thefunction returns anything other than zero. If it is not zero then we will get this value.*VLOOKUP* - Then, this formula checks the same thing with a smaller cell range,
then*B6:C10*and so on.*B7:C10*

- 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

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

*VLOOKUP***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 (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.

## 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!

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.