vlookup returning 0 instead of blank

[bucci35]

Hi,
I have the following formula that is returning a 0 when the item can be found but the second cell over is blank. I want it to return blank.

IF(ISERROR(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,)),"",(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,0)))

I want this to return a blank when the item in C7 can't be found PLUS if the item in C7 can be found but the second cell over is blank.
Any ideas what I'm doing wrong here??

Thanks,
dan

 

[jproffer]

If the item is found but the second column is empty, that's not an error.

Maybe:

=IF(OR(ISERROR(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,)),VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,)=0),"",(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,0)))

 

[Symon]

It isn't returning "" as there is no error, it's just found 0! Quickest fix is this:

IF(IF(ISERROR(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,)),"",(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,0)))=0,"",IF(ISERROR(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,)),"",(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,0)))

Sorry just typed this raw as I don't have a spreadsheet with these ranges but it should be ok...

Edit: jproffer 's formula is much simpler

 

[taurean]

Hi,
I have the following formula that is returning a 0 when the item can be found but the second cell over is blank. I want it to return blank.

IF(ISERROR(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,)),"",(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,0)))

I want this to return a blank when the item in C7 can't be found PLUS if the item in C7 can be found but the second cell over is blank.
Any ideas what I'm doing wrong here??

Thanks,
dan

It will give you blank when VLOOKUP will error out i.e. won't find data.

At this point, it finds data alright but it does not have any value against the Data LOOKED UP!

You can make it look blank using
Format Cells | Custom and type 0;-0; and Apply. Or you will have to incorporate in the formula itself.

 

[bucci35]

This gets rid of the 0 return but now returns a TRUE word when in fact something is found in the second cell over??

 

[bucci35]

The above answer was in reference to symon's suggestion.

 

[Aladin Akyurek]

Hi,
I have the following formula that is returning a 0 when the item can be found but the second cell over is blank. I want it to return blank.

IF(ISERROR(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,)),"",(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,0)))

I want this to return a blank when the item in C7 can't be found PLUS if the item in C7 can be found but the second cell over is blank.
Any ideas what I'm doing wrong here??

Thanks,
dan

Not very efficient...

=IF(COUNTIF(Sheet2!$D$3:$D$5000,$C7),
    IF(VLOOKUP($C7,Sheet2!$D$3:$H$5000,2,0)="","",
       VLOOKUP($C7,Sheet2!$D$3:$H$5000,2,0)),
    "")

If VLOOKUP is supposed to return a text value...

=IF(ISNA(VLOOKUP(VLOOKUP($C7,Sheet2!$D$3:$D$5000,1,0)),"",
    T(VLOOKUP($C7,Sheet2!$D$3:$H$5000,2,0))

If VLOOKUP is supposed to return a numeric value...

=IF(ISNA(VLOOKUP(VLOOKUP($C7,Sheet2!$D$3:$D$5000,1,0)),"",
   IF(VLOOKUP($C7,Sheet2!$D$3:$H$5000,2,0)="","",
     VLOOKUP($C7,Sheet2!$D$3:$H$5000,2,0)))

If you are on Excel 2007 or later...

=IFERROR(IF(VLOOKUP($C7,Sheet2!$D$3:$H$5000,2,0)="","",
   VLOOKUP($C7,Sheet2!$D$3:$H$5000,2,0)),"")

 

[Derek Brown]

This will happen when looking up data on another worksheet, if the cell is empty.
The answer is to check the length of the cell.
I have not tested it but expect it to be something like:
If(len(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,0))=0,"",VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,0))

 

[T. Valko]

Hi,
I have the following formula that is returning a 0 when the item can be found but the second cell over is blank. I want it to return blank.

IF(ISERROR(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,)),"",(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,0)))

I want this to return a blank when the item in C7 can't be found PLUS if the item in C7 can be found but the second cell over is blank.
Any ideas what I'm doing wrong here??

Thanks,
dan

What version of Excel are you using?

What type of data does the formula normally return? Is it text? Numbers? Could be both?

 

[Symon]

Really? I just replicated the tables and this works ok for me:

=IF(IF(ISERROR(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,)),"",(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,0)))=0,"",IF(ISERROR(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,)),"",(VLOOKUP(C7,Sheet2!$D$3:$H$5000,2,0))))

Not sure if it's identical to my original typed version to be fair, but in all honesty jproffers method is definitely the most efficient if mine doesnt work.