Vlookup returning 0

[gtdean11]

So I have a large table that I'm vlooking off of, and I'd like to return an empty cell instead of zero if the cell in the large table is empty.

I know I can use the formula =if(vlookup(A1,C1:Z999999,1,false)="","",vlookup(A1,C1:Z999999,1,false)) , however, I'm concerned that this would double the computation time. Is there a faster way to do this that will prevent excel from doing the vlookup twice? Preferably without putting the vlookup in a different cell.

Thanks!

[zbor]

No, you must do that twice.
But since you using 1 as column index you don't need VLOOKUP.

You can try: =IF(COUNTIF($C:$C,A1)=0,"",A1)

[dilipandey]

Hi gtdean11,

Welcome to the forum.

As per my knowledge, vlookup is generally slow as compared to Index - Match.. also just noticed that you are using Excel 2010.. why don't you use ISERROR function?

Regards,
DILIPandey

<click on below 'star' if this helps>

[Andrew-R]

Is that the actual formula you're using? VLOOKUP only does a look-up on the left-hand most column in an array, so specifying you're searching columns C-Z and then returning the information from column 1 means that you can only be returning a blank if your look-up value is blank. In which case you could use:

=IF(A1="","",A1)

That should use a lot less computational power.

[gtdean11]

Thanks for your help!

All: I gave a poor example of the formula. a more appropriate example would be =if(vlookup(A1,C1:Z999999,20,false)="","",vlookup(A1,C1:Z999999,20,false)). I'm not just looking to return the original value.

Given that, it looks like the answer is "no it's not possible, but a combination of index and match would be faster"

DILIPandey: how would the iserror function help, since it currently returns 0?

[Richard Buttrey]

Hi,

=ISERROR(INDEX(V:V,MATCH(A1,C:C,FALSE)),"")

[zbor]

This will still return 0 in case that there is a blank cell.

I would go with two VLOOKUP function as gtdean11 assumed in a frist place

[Richard Buttrey]

Sorry my apologies. It should have been an IFERROR not ISERROR. i.e.

=IFERROR(INDEX(V:V,MATCH(A1,C:C,FALSE)),"")

[Andrew-R]

That will still return a zero, because the VLOOKUP isn't generating an error.

If all of the returned values are whole numbers or text you could use:

=TEXT(VLOOKUP(A1,C1:Z999999,20,FALSE),"#")

[dilipandey]

Hi gtdean,

why don't you use ISERROR function?

Typo: I meant IFERROR function since you are using Excel 2010. Same has been been explained by Richard as well in post#8.

Thanks Richard.

Regards,
DILIPandey

<click on below 'star' if this helps>