Vlookup ignore 0 and N/A

[willstyle]

Hi All

Losing my mind with this.

Trying to vlookup whilst ignore #N/A and 0 to return blank

i know that: -

IF(VLOOKUP(C5,Raw3!$B$2:$J$20000,6,FALSE)=0,"",VLOOKUP(C5,Raw3!$B$2:$J$20000,6,FALSE)) will ignore 0 and return blank

and

IF(ISNA(VLOOKUP(C5,Raw3!$B$2:$I$5000,6,FALSE)),"",VLOOKUP(C5,Raw3!$B$2:$I$5000,6,FALSE)) will ignore #N/A and return blank

but how to i combine them so it will return a blank with 0 and #n/a

cheers in advance!!

 

[haddonsman]

Try:

=IF(ISERROR(VLOOKUP(C5,Raw3!$B$2:$I$5000,6,FALSE)),"",IF(VLOOKUP(C5,Raw3!$B$2:$I$5000,6,FALSE)=0,"",(VLOOKUP(C5,Raw3!$B$2:$I$5000,6,FALSE))))

 

[jim may]

You could also try:

=IF(OR(ISERROR(VLOOKUP(C5,Raw3!$B$2:$I$5000,6,FALSE)),VLOOKUP(C5,Raw3!$B$2:$I$5000,6,FALSE)=0),"",VLOOKUP(C5,Raw3!$B$2:$I$5000,6,FALSE))

 

[njimack]

I tend to go with COUNTIF to avoid 3 VLOOKUP's.

=IF(COUNTIF(C5,Raw3!B2:B5000),VLOOKUP(C5,Raw3!$B$2:$I$5000,6,FALSE),0)