VLOOKUP without #n/a and zeros

[Guest]

Hello,

I am using VLOOKUP to look up data between two worksheets within a workbook but am having trouble elimanating zeros and #n/a. I have tried an OR function but it doesn't seem to work. If there is a number or a zero the formula below works but I still get #N/A when a reference can not be found. If the OR function is removed and only the ISERROR function is used the #N/A's disappear and the zeros return.

The full formula:
=IF(OR(VLOOKUP(B151,'Sheet2'!$B$6:$H$100,5,FALSE)<1,ISERROR(VLOOKUP(B151,'Sheet2'!$B$6:$H$100,5,FALSE))),"",(VLOOKUP(B151,'Sheet2'!$B$6:$H$100,5,FALSE)))

Thank you for any assistance you may be able to provide!

-Greg

 

[Frank Kabel]

Hi
try
=IF(ISERROR(VLOOKUP(B151,'Sheet2'!
$B$6:$H$100,5,FALSE)),"",IF(VLOOKUP(B151,'Sheet2'!
$B$6:$H$100,5,FALSE)=0,"",VLOOKUP(B151,'Sheet2'!
$B$6:$H$100,5,FALSE))

If speed is an issue you may consider using a separate
cell storing the VLOOKUP result and only testing this. e.g.
M1:
=VLOOKUP(B151,'Sheet2'!$B$6:$H$100,5,FALSE)
N1:
=IF(ISERROR(M1),"",IF(M1=0,"",M1))

-----Original Message-----
Hello,

I am using VLOOKUP to look up data between two worksheets

within a workbook but am having trouble elimanating zeros
and #n/a. I have tried an OR function but it doesn't seem
to work. If there is a number or a zero the formula below
works but I still get #N/A when a reference can not be
found. If the OR function is removed and only the ISERROR
function is used the #N/A's disappear and the zeros
return.

 

[JulieD]

Hi Greg

try
=IF(ISNA(VLOOKUP(B151,'Sheet2'!$B$6:$H$100,5,FALSE)),"",IF(VLOOKUP(B151,'She
et2'!$B$6:$H$100,5,FALSE)=0,"",VLOOKUP(B151,'Sheet2'!$B$6:$H$100,5,FALSE)))

Cheers
JulieD

Hello,

I am using VLOOKUP to look up data between two worksheets within a

workbook but am having trouble elimanating zeros and #n/a. I have tried an
OR function but it doesn't seem to work. If there is a number or a zero the
formula below works but I still get #N/A when a reference can not be found.
If the OR function is removed and only the ISERROR function is used the
#N/A's disappear and the zeros return.

 

[RagDyeR]

Try This:

=IF(ISNA(MATCH(B151,Sheet2!$B$6:$B$100,0)),"",VLOOKUP(B151,Sheet2!$B$6:$H$10
0,5,FALSE))

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hello,

I am using VLOOKUP to look up data between two worksheets within a workbook
but am having trouble elimanating zeros and #n/a. I have tried an OR
function but it doesn't seem to work. If there is a number or a zero the
formula below works but I still get #N/A when a reference can not be found.
If the OR function is removed and only the ISERROR function is used the
#N/A's disappear and the zeros return.

The full formula:
=IF(OR(VLOOKUP(B151,'Sheet2'!$B$6:$H$100,5,FALSE)<1,ISERROR(VLOOKUP(B151,'Sh
eet2'!$B$6:$H$100,5,FALSE))),"",(VLOOKUP(B151,'Sheet2'!$B$6:$H$100,5,FALSE))
)

Thank you for any assistance you may be able to provide!

-Greg