vlookup + if isna formula should return blank if 0 but dosn't??

[mikestav1]

Hi,

I have a fairy large workbook with about 20 sheets containing data + formulas and a "master" sheet to suck out total qty's from all the other sheets. I'm having problems with a =IF(ISNA(VLOOKUP formula that is on this master page (probably already in over 1000 cells) I was previously using a older version and now running 2007 opening the workbook in compatability mode.

Here is the example of the formula working from the older version
=IF(ISNA(VLOOKUP($A8,Aleksandar!$A$6:$C$304,3,0)),"",VLOOKUP($A8,Aleksandar!$A$6:$C$304,3,0))

And here is the same formula im having problems with the only difference is its looking up from a different sheet and different range of cells
=IF(ISNA(VLOOKUP($A8,Angwin!$A$1:$R$37,3,0)),"",VLOOKUP(A8,Angwin!$A$1:$R$37,3,0))

The original formula is showing 0's from the other sheets and blank cells and the new formula is showing 0's from the other sheets as 0's!!!!

Is there some setting i need to change in 2007 or am i missing somthing?

Your reply is much appriciated.

 

[sulakvea]

for one thing you are missing the $ sign... so if you copy the formula down, it will change the reference - that might be an issue

=IF(ISNA(VLOOKUP($A8,Angwin!$A$1:$R$37,3,0)),"",VLOOKUP(A8,Angwin!$A$1:$R$37,3,0))

not sure if it is something 2007 related. i use 2003

 

[mikestav1]

Didnt notice the $ was missing but still no good, its bizzare as all the cells surrounding the one im trying to enter this new formula into has the same formula just looking at differnt sheets and are showing as blanks if the value is 0.

 

[sulakvea]

since it is showing blanks, i would check (you probably already have checked, so i would just double check) that the match exists. you have specifically indicated in the formula that you need a blank ("") if the vlookup cant find anything (ISERROR part). check for all sorts of discrepancies, like numbers entered as text, etc

 

[Aladin Akyurek]

If the VLOOKUP($A8,Aleksandar!$A$6:$C$304,3,0) bit returns 0, due to an empty cell or a real 0, you'll get 0 as result. A result of "" (a formula blank) occurs only when the lookup value in A8 does not match the first column of the range of interest.

I'd suggest invoking the following for all versions...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
    VLOOKUP($A8,Aleksandar!$A$6:$C$304,3,0)))

and custom format the formula cell as:

[=0]"";General

On Excel 2007 or beyond (native mode)...

=IFERROR(IF(VLOOKUP($A8,Aleksandar!$A$6:$C$304,3,0)=0,"",
    VLOOKUP($A8,Aleksandar!$A$6:$C$304,3,0)),"")

 

[mikestav1]

Works a treat thanks all.