Shahriar Abrar Rafid
Need to learn how to use XLOOKUP to return blank instead of 0? XLOOKUP is a widely used function. Using this function, we can extract data from one dataset to another. However, the XLOOKUP function returns us 0 when it can’t find any result. But, sometimes, we need blank cells at the position of empty cells. If you are looking for such unique tricks, you’ve come to the right place. Here, we will take you through 12 easy and convenient ways to use the XLOOKUP function to return blank instead of 0.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
12 Ways of Using XLOOKUP to Return Blank Instead of 0
Suppose we have a Daily Sales Report- Fruit Section of a certain grocery store. It contains the names of Sales Reps, their corresponding Product Names, and their respective Sales.
Now, we’ll apply the XLOOKUP function in the range of cells G5:G6, and the function returns us a 0 value. Also, we will show you how XLOOKUP will return blank cells instead of 0.
1. Utilizing an Optional Argument of XLOOKUP Function
In this method, we are going to use the XLOOKUP function to get blank instead of 0. The steps of this process are given below:
📌 Steps
- At first, select cell G5.
- Secondly, write down the formula below.
=XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14,"")
Formula Breakdown
Here, F5 represents the lookup_value. In this case, it is Alex.
B5:B14 is the lookup_array. In this dataset, it’s the names of the Sales Rep.
D5:D14 is the return_array, where the function looks for the result. In our situation, it’s the Sales amount.
We used “” for [if_not_found]. So, if the function can’t find any match, it’ll return a blank space in the output cell.
The dollar (﹩) sign is used to give absolute reference.
- Then, press ENTER.
- Next, double-click on the Fill Handle icon to copy the formula up to cell G6.
- You will get the blank cell for the two values.
Here, cell G6 has output because it’s present in Column B and has its respective Sales amount.
Read More: Excel IFERROR Function to Return Blank Instead of 0
2. Using Advanced Options to Make XLOOKUP Return Blank Instead of 0
You can make the XLOOKUP function return blank cells instead of 0 in an elegant way. You can utilize the advanced Excel options to do that. Follow the steps below.
📌 Steps
- Firstly, select cell G5.
- Secondly, paste the following formula into the Formula Bar.
=XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)
It’s the same formula that we’ve used in Method 1.
- Then, press the ENTER key.
- At this moment, go to the File tab.
- Next, select Options from the menu.
- Suddenly, the Excel Options window will open.
- Then, move to the Advanced tab,
- Later, uncheck the box of Show a zero in cells that have zero value under the section of Display options for this worksheet.
- Finally, click OK.
- At this point, you will get the two cells blank.
Read More: How to Apply VLOOKUP to Return Blank Instead of 0 or NA
3. Using Custom Number Format
Another option for the XLOOKUP function returning blank instead of 0 is to use a custom number format. Let’s go through the procedure below.
📌 Steps
- At the very beginning, select cell G5.
- Then, write down the following formula.
=XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)
It’s the same formula that we’ve used in Method 1.
- After that, hit the ENTER button.
- Now, select cells in the G5:G6 range.
- Then, press CTRL+1 on your keyboard.
- Hence, It will open up the Format Cells wizard.
- At this point, select Custom in the Category list.
- Then, write down 0;-0;;@ in the Type box.
- Lastly, click on OK.
- It will return us to the worksheet.
- And, we can see that the two cells are showing blank.
Read More: How to Leave Cell Blank If There Is No Data in Excel (5 Ways)
4. Applying Conditional Formatting
We can solve the problem by applying the Conditional Formatting rules. Let’s explore the method step by step.
📌 Steps
- At first, select cell G5 and write down the formula the same as Method 1.
=XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14,"")
- Secondly, press ENTER.
- Later, select cells in the B4:G14 range.
- Next, go to the Home tab.
- After that, select the Conditional Formatting drop-down on the Styles group.
- Lastly, choose New Rule from the drop-down list.
- Eventually, it will open up the New Formatting Rule dialog box.
- Now, select Format only cells that contain under the Select a Rule Type section.
- Then, choose equal to from the list.
- After that, write down 0 in the box as in the image below.
- Later, click on the Format button.
- However, it opens the Format Cells dialog box.
- Firstly, go to the Font tab.
- Secondly, select the Color drop-down list.
- Thirdly, choose White, Background 1 from the available colors.
- Lastly, click OK.
- It returns us to the New Formatting Rule dialog box again.
- Finally, click on OK.
- However, we can see these cells blank as in the image below.
Similar Readings
- How to Ignore Blank Cells in Excel Bar Chart (4 Easy Methods)
- Ignore Blank Series in Legend of Excel Chart
- How to Hide Zero Values in Excel Pivot Table (3 Easy Methods)
5. Using IF and XLOOKUP Functions to Return Blank Instead of 0
In this method, we are going to use the IF and XLOOKUP functions to get blank instead of 0. The steps of this process are given below:
📌 Steps
- First of all, select cell G5.
- Now, write down the following formula into the cell.
=IF(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)="","",XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14))
XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:B14, and it will print the corresponding value in the range of cells D5:D14. As the value in Column D for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
IF(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)=””,””,XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): The IF function first checks the value of the XLOOKUP function. If the XLOOKUP function returns blank or the logic is true, the IF function returns blank in cell G5. On the other hand, if the logic is false, the function returns the value of the XLOOKUP function.
- After that, press ENTER.
- You will see the formula returns us a blank cell instead of 0.
- Then, double-click on the Fill Handle icon to copy the formula up to cell G6.
- You will get the blank cell for the two values.
Thus, we can say that our formula worked perfectly, and XLOOKUP returns blank instead of 0.
6. Utilizing IF, LEN, and XLOOKUP Functions
In this process, we will use the IF, LEN, and XLOOKUP functions to get blank instead of 0. The steps of this approach are given as follows:
📌 Steps
- Firstly, select cell G5.
- After that, write down the following formula into the cell.
=IF(LEN(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14))=0,"",XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14))
XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:B14, and it will print the corresponding value in the range of cells D5:D14. As the value in Column D for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
LEN(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): This function counts the character length of the result got from the XLOOKUP function. In this case, the value is 0.
IF(LEN(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14))=0,””,XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): The IF function first checks the value of the LEN function. If the result of the LEN function is 0 or the logic is true, the IF function returns blank in cell G5. On the other hand, if the logic is false, the function returns the value of the XLOOKUP function.
- After that, press the ENTER key.
- Now, use the Fill Handle icon and get blank cells for the two values.
7. Applying IF, LET, and XLOOKUP Functions to Return Blank Instead of 0
In this approach, the IF, LET, and XLOOKUP functions will help us to get blank instead of 0. The steps of this procedure are given below:
📌 Steps
- Firstly, select cell G5.
- After that, write down the following formula into the cell.
=LET(x,XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),IF(x="","",x))
XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:B14, and it will print the corresponding value in the range of cells D5:D14. As the value in Column D for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
LET(x,XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),IF(x=””,””,x)): The LET function creates a variable named x. Then, it used the result from the XLOOKUP function to assign the value of x. After that, using the IF function, we inserted a logic. If x is empty, then return an empty string (“”). Otherwise, return the value of x.
- Then, hit the ENTER key on your keyboard.
- Hence, the final output looks like the one below.
8. Employing IF, ISBLANK, and XLOOKUP Functions
In this approach, the IF, ISBLANK, and XLOOKUP functions will help us to get blank instead of 0. The steps of this procedure are given below:
📌 Steps
- At first, select cell G5 and write down the following formula into the cell.
=IF(ISBLANK(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)),"",XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14))
XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:B14, and it will print the corresponding value in the range of cells D5:D14. As the value in Column D for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
ISBLANK(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): This function checks the result from the XLOOKUP function. If the cell is empty the function will return TRUE. Otherwise, it will return FALSE. In this case, the value is TRUE.
IF(ISBLANK(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)),””,XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): The IF function first checks the value of the ISBLANK function. If the result of the ISBLANK function is true, the IF function returns blank in cell G5. On the other hand, if the logic is false, the function returns the value of the XLOOKUP function.
- Later, press ENTER.
- Hence, the final output looks like the one below.
Similar Readings
- How to Remove Zeros in Front of a Number in Excel (6 Easy Ways)
- Hide Rows with Zero Values in Excel Using Macro (3 Ways)
- How to Hide Chart Series with No Data in Excel (4 Easy Methods)
9. Implementing IF, ISNUMBER, and XLOOKUP Functions to Return Blank Instead of 0
In this procedure, we are going to use the IF, ISNUMBER, and XLOOKUP functions to get blank instead of 0. The steps of this process are explained below:
📌 Steps
- In the beginning, select cell G5.
- Now, write down the following formula into the cell.
=IF(ISNUMBER(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)),XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),"")
XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:B14, and it will print the corresponding value in the range of cells D5:D14. As the value in Column D for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
ISNUMBER(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): This function checks the result got from the XLOOKUP function. If the cell is empty the function will return FALSE. Otherwise, it will return TRUE. In this case, the value is FALSE.
IF(ISNUMBER(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)),XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),””): The IF function first checks the value of the ISNUMBER function. If the result of the ISNUMBER function is FALSE, the IF function returns blank in cell G5. On the other hand, if the logic is TURE, the function returns the value of the XLOOKUP function.
- Later, press the ENTER key.
- Thus, the final output looks like the one below.
10. Combining IF, IFNA, and XLOOKUP Functions
In this case, we are going to use the combination IF, IFNA, and XLOOKUP functions to get blank instead of 0. The steps of this method are as follows:
📌 Steps
- At the beginning of this method, select cell G5.
- Then, write down the following formula into the cell.
=IF(IFNA(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),0)=0,"",XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14))
XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:B14, and it will print the corresponding value in the range of cells D5:D14. As the value in Column D for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
IFNA(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),0): This function counts the character length of the result got from the XLOOKUP function. In this case, the value is 0.
IF(IFNA(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),0)=0,””,XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): The IF function first checks the value of the IFNA function. If the result of the IFNA function is 0, the IF function returns blank in cell G5. Otherwise, the function returns the value of the XLOOKUP function.
- Lastly, press ENTER.
- Thus, the final output looks like the one below.
11. Using IFERROR and XLOOKUP Functions
In the following method, we will use the IFERROR and XLOOKUP functions to get blank instead of 0. We have to look for that value that doesn’t exist in our dataset. In such a case, the formula will return a blank cell instead of 0. The steps of this method are given as follows:
📌 Steps
- Primarily, select cell G5.
- Now, write down the following formula into the cell.
=IFERROR(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),"")
XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:B14, and it will print the corresponding value in the range of cells D5:D14. As the value in Column D for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
IFERROR(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),””): The IFERROR function first checks the value of the XLOOKUP function. If the result of the XLOOKUP function is 0, the IFERROR function returns blank in cell G5. Otherwise, the function returns the value of the XLOOKUP function.
- Simply, press the ENTER key.
Finally, we can say that our formula worked effectively, and XLOOKUP return blank instead of 0.
12. Utilizing IF, IFERROR, LEN, and XLOOKUP Functions to Return Blank Instead of 0
In the following approach, the IF, IFERROR, LEN, and XLOOKUP functions will help us to get the blank cell instead of 0. Let’s give the procedure below step-by-step:
📌 Steps
- At first, select cell G5.
- After that, write down the following formula into the cell.
=IFERROR(IF(LEN(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14))=0,"",XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)),"")
XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:B14, and it will print the corresponding value in the range of cells D5:D14. As the value in Column D for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
LEN(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): This function counts the character length of the result got from the XLOOKUP function. In this case, the value is 0.
IF(LEN(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14))=0,””,XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): The IF function first checks the value of the LEN function. If the result of the LEN function is 0 or the logic is true, the IF function returns blank in cell G5. On the other hand, if the logic is false, the function returns the value of the XLOOKUP function.
IFERROR(IF(LEN(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14))=0,””,XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)),””): This function checks the decision of the IF function. If the function returns a blank cell, the IFERROR function shows us the blank. Otherwise, the function will show the value of the corresponding cell in Column D.
- As always, press ENTER.
Thus, we can say that our formula worked successfully, and XLOOKUP returns blank instead of 0.
Practice Section
For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.
Conclusion
This article provides easy and brief solutions to how XLOOKUP will return blank instead of 0. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website Exceldemy to explore more.
First of all, many many thanks for this piece of writing. You have explained all the items in details.
A few days ago I was trying to solve a problem, but couldn’t.
This is like: in column A, i have A,B,C,B,A,C,B,A,C in order. Like A is in cell A1, B is in cell B2 and just like that. In column B, i’ve their respective value. These are like 180,360,200,400,203,350,160,500,233. From the above informations, iwant to find the minimum values of A.B,C.
I have tried using vlookup, but it can’t get the correct answer.
Would you please enlighten me how I can get the min value?
TIA
Hello Siam A,
In the first place, thanks for your this kind of support. This is what motivates us to move forward.
Now, let’s get back to your problem. Here, I’ve created a dataset from the information you provided in the comment. Get a look at the dataset first.
Then, in cell F5, we’ll fetch the minimum value of A. As the dataset is small enough, we can see that the min value of A is 180. Let’s see if we get the same value with our formula.
Firstly, select cell F5 and write down the following formula into the cell.
=IF(B5:B13="A",C5:C13)Then, press ENTER.
Here, we got an array in Column F. If the corresponding cell in Column B holds A, then in the cell in Column F, we get the consecutive value of A. Otherwise, it returns FALSE.
After that, apply the MIN function with the formula to find the minimum value from the array.
So, again, go to cell F5 and edit the formula. Now, it’ll look like the one below.
=MIN(IF(B5:B13="A",C5:C13))Thus, press ENTER.
Finally, we’ve got the min value of A.
Similarly, we can obtain the min value of B. Just, select cell F6 and paste the following formula.
=MIN(IF(B5:B13="B",C5:C13))Then, press the ENTER key.
Corresponding, get the minimum of value of C. Just write down C inside the double quote marks of the formula.
Note: The problem with VLOOKUP is that it always get the first value for the lookup value. For example, using the VLOOKUP function to get the minimum value of B, you’ll always receive 360. Because, after retrieving the value 360 it doesn’t go down further. But the correct result should be 160.
You can download the practice workbook for better understanding.
Hope you will find the solution helpful. Don’t forget to subscribe to our website Exceldemy: One-stop Excel sotuion provider…