Skip to the Next Result with VLOOKUP If a Blank Cell Is Present – 3 Methods

The first two methods show how to modify the VLOOKUP function so that it returns the next result if any rows are empty. The last method describes the steps to return non blank values from the next column.
This is the sample dataset.

VLOOKUP If Blank Skip to Next Result


Method 1- Combining the VLOOKUP and the IFNA Functions

Combine the VLOOKUP and IFNA functions.

Steps:

  • Enter “Ben” in B13.
  • Enter the following formula in C13.

=IFNA(VLOOKUP(B13,B5:C10,2,1),VLOOKUP(B13,B5:C10,2,0))

Combining VLOOKUP and IFNA Functions

  • Press ENTER.
  • This is the output.

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.
    • matches the value in B13 with B5:C10. It returns the value from the second column. The range_lookup is set as an approximate match by typing 1.
  • VLOOKUP(B13,B5:C10,2,0)
    • Output: 0.
    • matches the value in B13 with B5:C10. It returns the value from the second column. The range_lookup is set as an exact match by typing 0.
  • The formula reduces to → IFNA(6120,0)
    • Output: 6120.
    • If “#N/A” is returned by the first VLOOKUP function, you will get the second value from this function.

Read More: How to Skip Every Other Column Using Excel Formula


Method 2 – Combining the IFS and VLOOKUP Functions to Skip to the Next Result

Combine the IFS and VLOOKUP functions.

Steps:

  • Enter the following formula in 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

  • Press ENTER.
  • This is the output.

Formula Breakdown

  • The IFS function allows more simple formulas than the nested IF statement.
  • VLOOKUP(B13,B5:C10,2,0)<>0
    • Output: FALSE.
    • This logical_test checks if the VLOOKUP function returns a value other than zero.
    • Then, the formula searches values other than zero within a smaller range, B6:C10, B7:C10 and so on.
  • 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.

Read More: How to Skip Columns in Excel Formula


Method 3 – Skipping to the Next Result by Merging the IF and the VLOOKUP Functions

Merge the IF and VLOOKUP functions.

Steps:

  • Enter the following formula in 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

  • Press ENTER.
  • The 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

Practice here.

Practice Dataset


Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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