How to Use XLOOKUP to Return Blank Instead of 0 -12 Methods

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

XLOOKUP Return Blank Instead of 0

 


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.

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

One of the cells will be blank.

XLOOKUP Return Blank Instead of 0

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.

XLOOKUP Return Blank Instead of 0

  • Go to the File tab.

XLOOKUP Return Blank Instead of 0

  • Select Options from the menu.

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

The two cells will be blank.

XLOOKUP Return Blank Instead of 0


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.

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

The two cells are blank in your worksheet.

XLOOKUP Return Blank Instead of 0


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.

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0

Cells are blank in the output.

XLOOKUP Return Blank Instead of 0


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.

XLOOKUP Return Blank Instead of 0

A blank cell is displayed instead of 0.

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

XLOOKUP Return Blank Instead of 0

Blank cells will be displayed for the two values.

XLOOKUP Return Blank Instead of 0


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.

XLOOKUP Return Blank Instead of 0

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

XLOOKUP Return Blank Instead of 0


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.

XLOOKUP Return Blank Instead of 0

This is the output.

Applying IF, LET Functions


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.

Applying IF, ISBLANK Functions

This is the final output.

Applying IF, ISBLANK Functions


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.

Applying IF, ISNUMBER Functions

This is the final output.

Applying IF, ISNUMBER Functions


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.

Applying IF, IFNA Functions

This is the final output.

Applying IF, IFNA Functions


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.

Applying IFERROR Function

This is the output.Applying IFERROR Function


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.

Applying IF, IFERROR, LEN Functions

This is the output.

Applying IF, IFERROR, LEN Functions


Practice Section

Practice here.

Practice section


Download Practice Workbook

Download the following Excel workbook to practise.

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo