This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I am bringing data from one sheet to another based on matched ID's. Where the ID doesn't exist it is returning blanks or #N/A which is fine, but if the ID exists and the cell I am looking at is blank it return a 0 instead of a blank - some of my cells have 0 in them so I can't differentiate between what should be a 0 and what should be a blank. See example below?
| ID | Service 1 | Number | Minutes | Service 2 | Number | Minutes |
|---|---|---|---|---|---|---|
| 10-101 | Service Type A | 2 | 70 | Service TYPE B | 4 | 120 |
| 10-102 | Service TYPE B | 1 | 30 | |||
| 10-103 | Service TYPE B | 1 | 45 | |||
| 10-104 | Service TYPE B | 1 | 60 | Service Type C | 2 | 0 |
| 10-105 | Service Type A | 1 | 0 | Service TYPE B | 2 | 60 |
| 10-106 | Service Type C | 1 | 30 | |||
| 10-107 | Service TYPE B | 1 | 0 | Service TYPE B | 1 | 60 |
using the formula of =VLOOKUP(A2,...,2,FALSE) etc. it's returning this:
| ID | Service 1 | Number | Minutes | Service 2 | Number | Minutes |
|---|---|---|---|---|---|---|
| 10-101 | Service Type A | 2 | 70 | Service TYPE B | 4 | 120 |
| 10-102 | Service TYPE B | 1 | 30 | 0 | 0 | 0 |
| 10-103 | Service TYPE B | 1 | 45 | 0 | 0 | 0 |
| 10-104 | Service TYPE B | 1 | 60 | Service Type C | 2 | 0 |
| 10-105 | Service Type A | 1 | 0 | Service TYPE B | 2 | 60 |
| 10-106 | Service Type C | 1 | 30 | 0 | 0 | 0 |
| 10-107 | Service TYPE B | 1 | 0 | Service TYPE B | 1 | 60 |
whereas I want the highlighted zero's to be blank - any ideas??
Thanks
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
> .. if the ID exists and the cell I am looking at is blank it return a 0 instead of a blank
You could try something like this
=IF(VLOOKUP(D1,A:B,2,0)="","",VLOOKUP(D1,A:B,2,0))
to return the desired blanks ("") for matched cases with blanks in the return col
I'd say a SUBSTITUTE would be more efficient than an IF that contains two vlookup operations, like this:
=SUBSTITUTE(VLOOKUP(A1, $F1:$G$7, 2),"0","")
It's difficult to know how Excel processes the formula internally, but at least you know for sure that the expensive VLOOKUP is processed only once with the above.
That's dangerous. If your returned data contains 0 this will produce wrong result.
For numbers, Max's solution is the safe workaround.
If your data is text and you want a blank rather than a 0 in the result, you should do:
="" & VLOOKUP(......)
to eliminate the 0.
I'd say a SUBSTITUTE would be more efficient than an IF that contains two vlookup operations, like this:
=SUBSTITUTE(VLOOKUP(A1, $F1:$G$7, 2),"0","")
It's difficult to know how Excel processes the formula internally, but at least you know for sure that the expensive VLOOKUP is processed only once with the above.
Hi,
try append &"" at the end of VLOOKUP => =VLOOKUP(A2,...,2,FALSE)&""
Giuseppe
Excellent Giuseppe! This solution played it the best for what I needed. It returns blank for blank and the looked-up value for everything else which is not blank.
Hi,
try append &"" at the end of VLOOKUP => =VLOOKUP(A2,...,2,FALSE)&""
Giuseppe