Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup
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? |
#2
|
|||
|
|||
vlookup
Change TO:
=IF(OR(ISERROR(VLOOKUP(...)),VLOOKUP(...)=0,"",VLO OKUP(...)) 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 "Jim" wrote in message ... 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? |
#3
|
|||
|
|||
vlookup
I don't think that your OR will work the way you want if there is no match.
Anne Troy wrote: Change TO: =IF(OR(ISERROR(VLOOKUP(...)),VLOOKUP(...)=0,"",VLO OKUP(...)) 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 "Jim" wrote in message ... 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? -- Dave Peterson |
#4
|
|||
|
|||
vlookup
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.) Jim wrote: 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? -- Dave Peterson |
#5
|
|||
|
|||
vlookup
You're right, of course. Thanks, Dave. :)
************ Anne Troy www.OfficeArticles.com "Dave Peterson" wrote in message ... I don't think that your OR will work the way you want if there is no match. Anne Troy wrote: Change TO: =IF(OR(ISERROR(VLOOKUP(...)),VLOOKUP(...)=0,"",VLO OKUP(...)) 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 "Jim" wrote in message ... 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? -- Dave Peterson |
#6
|
|||
|
|||
vlookup
The first one is the charm, thanks
"Dave Peterson" wrote: 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.) Jim wrote: 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? -- Dave Peterson |
#7
|
|||
|
|||
vlookup
The second one is nicer!
Jim wrote: The first one is the charm, thanks "Dave Peterson" wrote: 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.) Jim wrote: 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? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |