Consider the following dataset containing a list of Ids, Products, and their corresponding Sales values. In the Product ID column and Product column, we have some blank cells. Using some formulas, we will skip these blank cells and retrieve values from the adjacent cells.
We used the Microsoft Excel 365 version in this article, but you can use any other version at your disposal.
Method 1 – Using IF Function
Let’s use the IF function to make a list of products which don’t have corresponding Product IDs. When there is no value in a cell in the Product ID column, we’ll move to the adjacent cell and extract the value into the List column.
Steps:
 Enter the following formula in cell E4:
=IF(B4="",C4,"")
Here, B4 is the Product ID, and C4 is the corresponding Product name. If cell B4 is blank, the formula returns the product name Apple, otherwise a blank.
 Press ENTER and drag down the Fill Handle to return the values for the rest of the column.
The following results are returned in the List column.
Read More: How to Skip Blank Rows Using Formula in Excel
Method 2 – Using IF and ISBLANK Functions
Here, we will extract the names of the products which don’t have corresponding Product IDs into the List column using a combination of the IF and ISBLANK functions.
Steps:
 Enter the following formula in cell E4:
=IF(ISBLANK(B4),C4,"")
Here, B4 is the Product ID, and C4 is the corresponding Product name. If cell B4 is blank, then the formula returns the product name Apple, otherwise a blank.
 Press ENTER and drag down the Fill Handle.
A list of names of products without ids is returned.
Read More: How to Skip a Column When Selecting in Excel
Method 3 – Using the IFS Function
Here, we will skip the blank cells in the Product ID column and move to the adjacent cell in the Product column to extract the name of the products, then gather them in the List column using the IF, ISNA, and IFS functions.
Steps:
 Enter the following formula in cell E4:
=IF(ISNA(IFS(B4="",C4,C4="","")),"",IFS(B4="",C4,C4="",""))
Here, B4 is the Product ID, and C4 is the corresponding Product name.
Formula Breakdown
 B4=””→ becomes
 123001=”” → if the value 123001 equates with a space, then it will return TRUE otherwise FALSE.
 Output → FALSE
 123001=”” → if the value 123001 equates with a space, then it will return TRUE otherwise FALSE.
 C4=””→ becomes
 Apple=”” → if the value Apple equates with a space, then it will return TRUE otherwise FALSE.
 Output → FALSE
 Apple=”” → if the value Apple equates with a space, then it will return TRUE otherwise FALSE.
 IFS(B4=””,C4,C4=””,””) → becomes
 IFS(FALSE, “Apple” ,FALSE,””) → returns a #N/A error because of the FALSE argument.
 Output → #N/A
 IFS(FALSE, “Apple” ,FALSE,””) → returns a #N/A error because of the FALSE argument.
 ISNA(IFS(B4=””,C4,C4=””,””)) → becomes
 ISNA(#N/A) → TRUE
 IF(ISNA(IFS(B4=””,C4,C4=””,””)),””,IFS(B4=””,C4,C4=””,””)) → becomes
 IF(TRUE,””,#N/A) → returns blank.
 Output → Blank
 IF(TRUE,””,#N/A) → returns blank.
 Press ENTER and drag down the Fill Handle.
A list of products without Product Ids is returned in the List column.
Method 4 – Using a Combination of IFERROR, VLOOKUP and IF Functions
Here, we will search for the sales values in the Sales column, and then using the IFERROR, VLOOKUP and IF functions, we will skip the blank cells of the Product column and move to the Sales column to extract the corresponding sales values.
We will extract the values from the Sales column into the following datasheet in a new sheet.
Steps:
 Enter the following formula in cell C4:
=IFERROR(IF(VLOOKUP(B4,Data!$B$3:$D$11,2,FALSE)&""="",IF(VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)&""="","",VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)),""),"")
Here, B4 is the Product ID.
Formula Breakdown
 VLOOKUP(B4,Data!$B$3:$D$11,2,FALSE) → becomes
 VLOOKUP(123001, Data!$B$3:$D$11,2, FALSE) → VLOOKUP will look for the value 123001 in the $B$3:$D$11 range of the Data sheet, the column index number is 2, and FALSE is for an exact match.
 Output → Apple
 VLOOKUP(123001, Data!$B$3:$D$11,2, FALSE) → VLOOKUP will look for the value 123001 in the $B$3:$D$11 range of the Data sheet, the column index number is 2, and FALSE is for an exact match.
 VLOOKUP(B4,Data!$B$3:$D$11,2,FALSE)&””=”” → becomes
 Apple&””=”” → “Apple”=”” → FALSE
 IF(VLOOKUP(B4,Data!$B$3:$D$11,2,FALSE)&””=””,IF(VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)&””=””,””,VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)),””) → becomes
 IF(FALSE,IF(VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)&””=””,””,VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)),””) → returns a Blank due to the FALSE argument.
 Output → “”
 IF(FALSE,IF(VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)&””=””,””,VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)),””) → returns a Blank due to the FALSE argument.
 IFERROR(IF(VLOOKUP(B4,Data!$B$3:$D$11,2,FALSE)&””=””,IF(VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)&””=””,””,VLOOKUP(B4,Data!$B$3:$D$11,3,FALSE)),””),””) → becomes
 IFERROR(“”,””) → returns a blank.
 Output → Blank
 IFERROR(“”,””) → returns a blank.
 Press ENTER and drag down the Fill Handle.
The sales values for the blank products are returned in the Sales column of the new sheet.
Read More: Skip to Next Result with VLOOKUP If Blank Cell Is Present
Method 5 – Using the IF and XLOOKUP Functions
Now we’ll apply the combination of the IF and XLOOKUP functions to return the name of the product just above a blank cell in the Product column in the List column. Then we will filter these values using the FILTER function.
Steps:
 Keep the initial cell of the List column Blank.
 Enter the following formula in cell D5:
=IF(B5=””,IF(B5=””,XLOOKUP(“*”,$B$4:B5,$B$4:B5,””,2,1),””),IF(B5<>””,””,IF(D4=””,””,B5)))
Formula Breakdown
 B5=”” → becomes
 “Orange”=”” → if the text Orange equates with a space, then it will return TRUE otherwise FALSE.
 Output → FALSE
 “Orange”=”” → if the text Orange equates with a space, then it will return TRUE otherwise FALSE.
 IF(B5=””,XLOOKUP(“*”,$B$4:B5,$B$4:B5,””,2,1) → becomes
 IF(FALSE,XLOOKUP(“*”,$B$4:B5,$B$4:B5,””,2,1)
 Output → #N/A
 IF(FALSE,XLOOKUP(“*”,$B$4:B5,$B$4:B5,””,2,1)
 B5<>”” → becomes
 “Orange”<>”” → if the text Orange equates with a space, then it will return TRUE otherwise FALSE.
 Output → TRUE
 “Orange”<>”” → if the text Orange equates with a space, then it will return TRUE otherwise FALSE.
 IF(D4=””,””,B5))) → becomes
 IF(TRUE,””,B5)))

 Output → “”

 IF(TRUE,””,B5)))
 IF(B5=””,IF(B5=””,XLOOKUP(“*”,$B$4:B5,$B$4:B5,””,2,1),””),IF(B5<>””,””,IF(D4=””,””,B5))) → becomes
 IF(FALSE, #N/A,””)


 Output → Blank


 IF(FALSE, #N/A,””)
 Press ENTER and drag down the Fill Handle.
The result is as follows:
In cell E4, use the following formula to remove the blanks from the List column:
=FILTER(D4:D11,D4:D11<>"")
Here, D4:D11 is the range in which we will be filtering out the blanks.
Read More: How to Skip Columns in Excel Formula
Download Practice Workbook
Related Articles
 Excel Formula to Skip Rows Based on Value
 How to Skip Hidden Cells When Pasting in Excel
 Skip Cells When Dragging in Excel
 How to Skip Cells in Excel Formula
 How to Skip Lines in Excel
<< Go Back to Excel Cells  Learn Excel
Get FREE Advanced Excel Exercises with Solutions!