If you need to learn how to use **XLOOKUP** to return blank instead of 0, you have come to the right place. **XLOOKUP** is a widely used function. Using this function, we can extract data from one dataset to another. However, the **XLOOKUP** function returns 0 when it can’t find any results. But, sometimes, we need blank cells in the place 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.

**Table of Contents**Expand

## How to Use XLOOKUP to Return Blank Instead of 0: 12 Practical Ways

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 a 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 matches, 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 a 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:** How to Use XLOOKUP Function with Multiple Criteria in Excel

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

### 3. Using Custom Number Format

Another option for the **XLOOKUP **function to return 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.

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

### 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 in 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 is located 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 in 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 is located 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 obtained 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 in 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 is located 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**

- First, select cell
**G5**and write down the following formula for 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**.

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

### 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 in 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.

- 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 in 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 obtained 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 in 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.

Finally, we can say that our formula worked effectively, and **XLOOKUP** returned 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 in 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 is located 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

**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 obtained 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 on each sheet on the right side. Please do it by yourself.

**Download Practice Workbook**

You may download the following Excel workbook for better understanding and practice 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 questions or suggestions.

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 ofA. As the dataset is small enough, we can see that the min value ofAis180. Let’s see if we get the same value with our formula.Firstly, select cell

F5and 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 inColumn BholdsA, then in the cell inColumn F, we get the consecutive value ofA. Otherwise, it returnsFALSE.After that, apply the

MIN functionwith the formula to find the minimum value from the array.So, again, go to cell

F5and 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 cellF6and paste the following formula.`=MIN(IF(B5:B13="B",C5:C13))`

Then, press the

ENTERkey.Corresponding, get the minimum of value of

C. Just write downCinside the double quote marks of the formula..Note:The problem withVLOOKUPis that it always get the first value for the lookup value. For example, using theVLOOKUP functionto get the minimum value ofB, you’ll always receive360. Because, after retrieving the value360it doesn’t go down further. But the correct result should be160You can download the

practice workbookfor better understanding.Hope you will find the solution helpful. Don’t forget to subscribe to our website

Exceldemy: One-stop Excel sotuion provider…