Vlookup formula to eliminate '0' values

[jjosey01]

Hi,

Could someone please help with the following formula?

=IF(COUNTBLANK(A2)=0,VLOOKUP(A2,Geology!$A$2:$D$7560,4,TRUE),"")

I would like to eliminate any '0' values in my results, however many '0' values are being returned because there are many blank ("") records in row D of the Geology! tab of the spreadsheet which the Vlookup result is referencing.

Could someone please help me to expand this formula to return a "" value when either cells A2 or Geology!(D2) is empty or 0 in the formula above?

Thanks very much for your help.

 

[s hal]

I'm not sure what countblank() is doing.. WHy not just say =if(A2="",vlookup(),"")?

That said, if you want to get rid of the vlookup() returning 0's, I'd probably do something like:
=iferror(if(vlookup(A2,Geology!$A$2:$D$7560,4,TRUE)=0,"",vlookup(A2,Geology!$A$2:$D$7560,4,TRUE)),"")

 

[Robert Mika]

Yuo can use
=if(a2=0,"",vlookup(A2,Geology!$A$2:$D$7560,4,TRUE))
and format cell as 0;-0;;@ (go to Custom Format when right clicking on cell and choosing Format Cells)
or to prevent the #N/A return when values is not found:=if(a2=0,"",iferror(vlookup(A2,Geology!$A$2:$D$7560,4,TRUE),"")
This will only work on Excel 07/10

 

[Aladin Akyurek]

Hi,

Could someone please help with the following formula?

=IF(COUNTBLANK(A2)=0,VLOOKUP(A2,Geology!$A$2:$D$7560,4,TRUE),"")

I would like to eliminate any '0' values in my results, however many '0' values are being returned because there are many blank ("") records in row D of the Geology! tab of the spreadsheet which the Vlookup result is referencing.

Could someone please help me to expand this formula to return a "" value when either cells A2 or Geology!(D2) is empty or 0 in the formula above?

Thanks very much for your help.

=IF(A2="","",IF(LOOKUP(A2,Geology!$A$2:$A$7560)="","",
    LOOKUP(A2,Geology!$A$2:$A$7560,Geology!$D$2:$D$7560)))

If the hits are text values (not numeric values)...

=IF(A2="","",T(LOOKUP(A2,Geology!$A$2:$A$7560,Geology!$D$2:$D$7560)))

 

[jjosey01]

Hi guys,

Thanks to everyone for your input, the codes posted have been extremely useful and have enabled me to eliminate the '0' values throughout my spreadsheet providing only the lookup value required.

Thanks Again!