vlookup - combine iferror and 0 result

[ajm]

I am having trouble getting the correct syntax for the following situation:

if the vlookup returns either 0 (where the lookup value is in the lookup array but without anything in the required column) or #N/A (where the lookup value is not in the look up arra at all), then give me a blank cell, else do the lookup

i have:

=IF(OR((VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0)=0),ISERROR(VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0))),"",VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0))

no matter how i play around with this, I can't get it to return a blank cell when either of my conditions is met. in isolation, the iserror test works fine,

IF(ISERROR(VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0)),"",VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0))

as does the lookup where no value is recorded in the lookup array for that lookup value.
IF(VLOOKUP($A5,'FY11 QME'!$A$7:$G$346,7,0)=0,"",VLOOKUP($A5,'FY11 QME'!$A$7:$G$346,7,0))

any ideas?

 

[T. Valko]

I am having trouble getting the correct syntax for the following situation:

if the vlookup returns either 0 (where the lookup value is in the lookup array but without anything in the required column) or #N/A (where the lookup value is not in the look up arra at all), then give me a blank cell, else do the lookup

i have:

=IF(OR((VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0)=0),ISERROR(VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0))),"",VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0))

no matter how i play around with this, I can't get it to return a blank cell when either of my conditions is met. in isolation, the iserror test works fine,

IF(ISERROR(VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0)),"",VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0))

as does the lookup where no value is recorded in the lookup array for that lookup value.
IF(VLOOKUP($A5,'FY11 QME'!$A$7:$G$346,7,0)=0,"",VLOOKUP($A5,'FY11 QME'!$A$7:$G$346,7,0))

any ideas?

You have to test for the "iserror" first.

One way:

=IF(COUNTIF('FY11 QME'!$A$7:$A$346,$A6),IF(VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0)="","",VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0)),"")

 

[T. Valko]

You have to test for the "iserror" first.

One way:

=IF(COUNTIF('FY11 QME'!$A$7:$A$346,$A6),IF(VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0)="","",VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0)),"")

If the value being returned is TEXT then we can shorten that a bit.

=IF(COUNTIF('FY11 QME'!$A$7:$A$346,$A6),T(VLOOKUP($A6,'FY11 QME'!$A$7:$G$346,7,0)),"")

 

[ajm]

my thanks Mr Valko. I have used your first version as I am looking for numnber of spaces in transit. cheers.

 

[T. Valko]

my thanks Mr Valko. I have used your first version as I am looking for numnber of spaces in transit. cheers.

You're welcome. Thanks for the feedback!