vlookup

[Guest]

I am using the following:

=IF(ISERROR(VLOOKUP(D6,Pri_Bus_Unit,2,FALSE)),"",(VLOOKUP(D6,Pri_Bus_Unit,2,FALSE)))

It is returning a 0 (zero) instead of a blank cell. The cells are formatted
as general. Even if I am using "-" a dash it is returning zero.

Any thoughts?

 

[Anne Troy]

Change TO:
=IF(OR(ISERROR(VLOOKUP(...)),VLOOKUP(...)=0,"",VLOOKUP(...))
If the value in D6 exists, but the value to its right in the vlookup table
is blank, that does NOT produce an error, so ISERROR and ISNA won't handle
it. You have to say if it's zero, you want nothing or Tools-->Options-->View
and uncheck zero values.
************
Anne Troy
www.OfficeArticles.com

 

[Dave Peterson]

I don't think that your OR will work the way you want if there is no match.

 

[Dave Peterson]

You could check (up to three times):

=IF(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"",
IF(VLOOKUP(A1,Sheet2!A:B,2,FALSE)="","",VLOOKUP(A1,Sheet2!A:B,2,FALSE)))

or since you want to see "", you could check up to twice:

=IF(ISERROR(1/LEN(VLOOKUP(A1,Sheet2!A:B,2,FALSE))),"",
VLOOKUP(A1,Sheet2!A:B,2,FALSE))

(change the range/range names to what you want.)

 

[Anne Troy]

You're right, of course. Thanks, Dave.
************
Anne Troy
www.OfficeArticles.com

 

[Guest]

The first one is the charm, thanks

You could check (up to three times):

=IF(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"",
IF(VLOOKUP(A1,Sheet2!A:B,2,FALSE)="","",VLOOKUP(A1,Sheet2!A:B,2,FALSE)))

or since you want to see "", you could check up to twice:

=IF(ISERROR(1/LEN(VLOOKUP(A1,Sheet2!A:B,2,FALSE))),"",
VLOOKUP(A1,Sheet2!A:B,2,FALSE))

(change the range/range names to what you want.)

 

[Dave Peterson]

The second one is nicer!

The first one is the charm, thanks