The following dataset contains the names of **Sales Reps**, **Product Names**, and **Sales**.

### Method 1 – Utilizing an Optional Argument of the XLOOKUP Function

**Steps**

- Select
**G5**. - Enter the formula below.

`=XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14,"")`

**Formula Breakdown
**Here,

**F5**represents the

**lookup_value**(

**Alex)**.

**B5:B14**is the

**lookup_array (**the names of the

**Sales Rep**).

**D5:D14**is the

**return_array**(

**Sales**amount).

We used

**“”**for

**[if_not_found]**. If the function can’t find any matches, it will return blank.

The dollar

**(**

**﹩**

**)**sign is used to provide an absolute reference.

- Press
**ENTER**.

- Double-click the
**Fill Handle**to copy the formula to cell**G6**.

One of the cells will be blank.

**G6** contains the 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

### Method 2 – Using Advanced Options to Make XLOOKUP Return Blank Instead of 0

**Steps**

- Select
**G5**. - Enter the following formula in the
**Formula Bar**.

`=XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)`

It’s the same formula used in **Method 1**.

- Press
**ENTER**.

- Go to the
**File**tab.

- Select
**Options**from the menu.

- The
**Excel Options**window will open. - Go to the
**Advanced**tab. - Uncheck the box
**Show a zero in cells that have zero value**under the section**Display options for this worksheet**. - Click
**OK**.

The two cells will be blank.

### Method 3 – Using a Custom Number Format

** Steps**

- Select
**G5**. - Enter the following formula.

`=XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)`

It’s the same formula used in **Method 1**.

- Press
**ENTER**.

- Select cells in the
**G5:G6**range. - Press
**CTRL+1**.

- The
**Format Cells**wizard will open. - In
**Category, s**elect**Custom**. - in
**Type, enter****0;-0;;@**. - Click
**OK**.

The two cells are blank in your worksheet.

### Method 4 – Applying Conditional Formatting

**Steps**

- Select cell
**G5**and enter the formula like in**Method 1**.

`=XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14,"")`

- Press
**ENTER**.

- Select cells in the
**B4:G14**range. - Go to the
**Home**tab. - In
**Styles**, select**Conditional Formatting**. - Choose
**New Rule**from the drop-down list.

- The
**New Formatting Rule**dialog box will open. - In
**Select a Rule Type**, choose**Format only cells that contain**. - Select
**equal to**from the list. - Enter
**0**in the box. - Click
**Format**.

- The
**Format Cells**dialog box will open. - Go to the
**Font**tab. - Select the
**Color**from the drop-down list. - Choose
**White, Background 1**. - Click
**OK**.

- The
**New Formatting Rule**dialog box will be displayed again. - Click
**OK**.

Cells are blank in the output.

### Method 5. Using the IF and XLOOKUP Functions to Return Blank Instead of 0

**Steps**

- Select
**G5**. - Enter 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):**looks for the value of cell

**F5**in your dataset, which is located in the range

**B5:B14**, and inserts the corresponding value in the range

**D5:D14**. As the value in

**Column D**for the value of

**F5**is blank, the function will return

**0**. Otherwise, it will provide the value.

**IF(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)=””,””,XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): **checks the value of the **XLOOKUP** function. If the **XLOOKUP** function returns blank or the logic is true, the **IF** function returns blank in **G5**. If the logic is false, it returns the value of the **XLOOKUP** function.

- Press
**ENTER**.

A blank cell is displayed instead of 0.

- Double-click the
**Fill Handle**to copy the formula to**G6**.

Blank cells will be displayed for the two values.

### Method 6. Utilizing the IF, LEN, and XLOOKUP Functions

**Steps**

- Select
**G5**. - Enter 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):**looks for the value of

**F5**in your dataset, which is located in the range

**B5:B14**, and it inserts the corresponding value in the range

**D5:D14**. As the value in

**Column D**for the value of

**F5**is blank, the function will return

**0**. Otherwise, it will provide the value.

**LEN(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): **counts the character length of the result obtained from the **XLOOKUP **function. Here, 0.

**IF(LEN(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14))=0,””,XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)):** 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 **G5**. If the logic is false, it returns the value of the **XLOOKUP** function.

- Press
**ENTER**.

- Drag the
**Fill Handle**to get blank cells for the two values.

### Method 7 – Applying the IF, LET, and XLOOKUP Functions to Return Blank Instead of 0

**Steps**

- Select
**G5**. - Enter 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):**looks for the value of

**F5**in your dataset, which is located in the range

**B5:B14**, and inserts the corresponding value in the range

**D5:D14**. As the value in

**Column D**for the value of

**F5**is blank, the function will return

**0**. Otherwise, it will provide the value.

**LET(x,XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),IF(x=””,””,x)):** creates a variable named **x** and uses the result from the **XLOOKUP **function to assign the value of **x**. With the **IF **function, If **x** is empty, an empty string (**“”**) is returned. Otherwise, it returns the value of **x**.

- Press
**ENTER**.

This is the output.

### Method 8 – Using the IF, ISBLANK, and XLOOKUP Functions

**Steps**

- Select
**G5**and enter the following formula.

`=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):**looks for the value of

**F5**in your dataset, which locates in the range

**B5:B14**, and inserts the corresponding value in the range

**D5:D14**. As the value in

**Column D**for the value of

**F5**is blank, the function will return

**0**. Otherwise, it will provide the value.

**ISBLANK(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): **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)):** checks the value of the **ISBLANK** function. If the result of the **ISBLANK** function is true, the **IF** function returns blank in **G5**. If the logic is **false**, the function returns the value of the **XLOOKUP **function.

- Press
**ENTER**.

This is the final output.

### Method 9 – Using the IF, ISNUMBER, and XLOOKUP Functions to Return Blank Instead of 0

**Steps**

- Select
**G5**. - Enter the following formula.

`=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):**looks for the value of

**F5**in your dataset, which locates in the range

**B5:B14**, and it inserts the corresponding value in the range

**D5:D14**. As the value in

**Column D**for the value of

**F5**is blank, the function will return

**0**. Otherwise, it will provide the value.

**ISNUMBER(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)): **checks the result 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),””):** checks the value of the **ISNUMBER** function. If the result of the **ISNUMBER** function is FALSE, the **IF** function returns blank in **G5**. If the logic is TRUE, it returns the value of the **XLOOKUP** function.

- Press
**ENTER**.

This is the final output.

### Method 10 – Combining the IF, IFNA, and XLOOKUP Functions

**Steps**

- Select cell
**G5**. - Enter 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):**looks for the value of cell

**F5**in your dataset, which locates in the range

**B5:B14**, and inserts the corresponding value in the range

**D5:D14**. As the value in

**Column D**for the value of

**F5**is blank, the function will return

**0**. Otherwise, it will provide the value.

**IFNA(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),0): **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)): **checks the value of the **IFNA** function. If the result is 0, the **IF** function returns blank in **G5**. Otherwise, the function returns the value of the **XLOOKUP** function.

- Press
**ENTER**.

This is the final output.

### Method 11. Using the IFERROR and the XLOOKUP Functions

**Steps**

- Select
**G5**. - Enter 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):**looks for the value of

**F5**in your dataset, which locates in the range

**B5:B14**, and inserts the corresponding value in the range

**D5:D14**. As the value in

**Column D**for the value of

**F5**is blank, the function will return

**0**. Otherwise, it will provide the value.

**IFERROR(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14),””): **checks the value of the **XLOOKUP** function. If the result of the **XLOOKUP** function is 0, the **IFERROR** function returns blank in **G5**. Otherwise, the function returns the value of the **XLOOKUP **function.

- Press
**ENTER**.

This is the output.

### Method 12 – Utilizing the IF, IFERROR, LEN, and XLOOKUP Functions to Return Blank Instead of 0

**Steps**

- Select
**G5**. - Enter 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):**looks for the value of

**F5**in your dataset, which is located in the range

**B5:B14**, and inserts the corresponding value in the range

**D5:D14**. As the value in

**Column D**for the value of

**F5**is blank, the function will return

**0**. Otherwise, it will provide the value.

**LEN(XLOOKUP(F5,$B$5:$B$14,$D$5:$D$14)):** 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)):** 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 **G5**. 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)),””): **checks the decision of the **IF **function. If the function returns a blank cell, the **IFERROR **function shows us the blank. Otherwise, it will show the value of the corresponding cell in **Column D**.

- Press
**ENTER**.

This is the output.

## Practice Section

Practice here.

**Download Practice Workbook**

Download the following Excel workbook to practise.

Get FREE Advanced Excel Exercises with Solutions!
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…Method 7 for the win!!!!!Great article, I used the LET function and it worked perfectly since my formula was long, I did not want to repeat it.

Many thanks!

Hello

AMG,You are most welcome. It’s great to hear that Method-7 was helpful to you and it worked perfectly. Keep learning Excel with ExcelDemy.

Regards

ExcelDemy