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.

**Table of Contents**hide

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

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

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

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

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

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

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

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

**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),"")`

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

- 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),"")`

**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** 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)),"")`

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

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