How to Use XLOOKUP to Return Blank Instead of 0

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.

XLOOKUP Return Blank Instead of 0

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.

XLOOKUP Return Blank Instead of 0

  • Next, double-click on the Fill Handle icon to copy the formula up to cell G6.

XLOOKUP Return Blank Instead of 0

XLOOKUP Return Blank Instead of 0

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.

XLOOKUP Return Blank Instead of 0

  • At this moment, go to the File tab.

XLOOKUP Return Blank Instead of 0

  • Next, select Options from the menu.

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

  • At this point, you will get the two cells blank.

XLOOKUP Return Blank Instead of 0

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.

XLOOKUP Return Blank Instead of 0

  • Now, select cells in the G5:G6 range.
  • Then, press CTRL+1 on your keyboard.

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

  • It will return us to the worksheet.
  • And, we can see that the two cells are showing blank.

XLOOKUP Return Blank Instead of 0

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.

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

  • It returns us to the New Formatting Rule dialog box again.
  • Finally, click on OK.

XLOOKUP Return Blank Instead of 0

  • However, we can see these cells blank as in the image below.

XLOOKUP Return Blank Instead of 0


Similar Readings


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))
Formula Breakdown
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.

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

  • You will get the blank cell for the two values.

XLOOKUP Return Blank Instead of 0

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))
Formula Breakdown
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.

XLOOKUP Return Blank Instead of 0

  • Now, use the Fill Handle icon and get blank cells for the two values.

XLOOKUP Return Blank Instead of 0


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))
Formula Breakdown
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.

XLOOKUP Return Blank Instead of 0

  • Hence, the final output looks like the one below.

Applying IF, LET Functions


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))
Formula Breakdown
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.

Applying IF, ISBLANK Functions

  • Hence, the final output looks like the one below.

Applying IF, ISBLANK Functions


Similar Readings


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),"")
Formula Breakdown
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.

Applying IF, ISNUMBER Functions

  • Thus, the final output looks like the one below.

Applying IF, ISNUMBER Functions


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))
Formula Breakdown
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.

Applying IF, IFNA Functions

  • Thus, the final output looks like the one below.

Applying IF, IFNA Functions


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),"")
Formula Breakdown
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.

Applying IFERROR Function

Finally, we can say that our formula worked effectively, and XLOOKUP return blank instead of 0.

Applying IFERROR Function


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)),"")
Formula Breakdown
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.

Applying IF, IFERROR, LEN Functions

Thus, we can say that our formula worked successfully, and XLOOKUP returns blank instead of 0.

Applying IF, IFERROR, LEN Functions


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.

Practice section


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.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

2 Comments
  1. 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.

      Dataset

      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)

      Entering IF Function

      Then, press ENTER.

      Result Array

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

      Entering MIN Function

      Thus, press ENTER.

      Min Value of A

      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.

      Min Value of B

      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…

Leave a reply

ExcelDemy
Logo