HAVE VLOOKUP RETURN 0 OR BLANK INSTEAD OF #N/a

how do i get the vlookup formula to return 0 or return
nothing instead of #n/a. i know it has something to do
with a vlookup formula nested in an "if"
statement...please advise...

thanks.
kp

One way:

=IF(ISNA(VLOOKUP(---),"",VLOOKUP(---))

or

=IF(COUNTIF(X,Y),VLOOKUP(---),"")

X = first column of vlookup range
Y = value to look up

HTH
Jason
Atlanta, GA

-----Original Message-----
how do i get the vlookup formula to return 0 or return
nothing instead of #n/a. i know it has something to do
with a vlookup formula nested in an "if"
statement...please advise...

thanks.
kp
.

A couple of options with increasing performance...

1]

=IF(ISNUMBER(MATCH(A2,$E$2:$E$20,0)),VLOOKUP(A2,$E $2:$G$20,3,0),"")

2]

=IF(ISNA(SETV(VLOOKUP(A2,$E$2:$G$20,3,0))),"",GETV ())

3]

B2:

=IF(ISNA(C2),"",C2)

C2:

=VLOOKUP(A2,$E$2:$G$20,3,0)

wrote in message
...
how do i get the vlookup formula to return 0 or return
nothing instead of #n/a. i know it has something to do
with a vlookup formula nested in an "if"
statement...please advise...

thanks.
kp

KP,

IF(ISNA(YourVlookupHere), "", YourVlookupHere)
OR
IF(ISNA(YourVlookupHere), 0, YourVlookupHere)

Dan E

wrote in message ...
how do i get the vlookup formula to return 0 or return
nothing instead of #n/a. i know it has something to do
with a vlookup formula nested in an "if"
statement...please advise...

thanks.
kp

Aladin,

Is SETV and GETV something new in office 2003 or is it an add-in
of some sort?? I've never seen it before and by the looks of it (ie.
the way you seem to be using it in your post) it looks pretty handy.

Dan E

"Aladin Akyurek" wrote in message ...
A couple of options with increasing performance...

1]

=IF(ISNUMBER(MATCH(A2,$E$2:$E$20,0)),VLOOKUP(A2,$E $2:$G$20,3,0),"")

2]

=IF(ISNA(SETV(VLOOKUP(A2,$E$2:$G$20,3,0))),"",GETV ())

3]

B2:

=IF(ISNA(C2),"",C2)

C2:

=VLOOKUP(A2,$E$2:$G$20,3,0)

wrote in message
...
how do i get the vlookup formula to return 0 or return
nothing instead of #n/a. i know it has something to do
with a vlookup formula nested in an "if"
statement...please advise...

thanks.
kp

Dan,

they are from Laurent Longre's add-in Morefunc


--

Regards,

Peo Sjoblom

"Dan E" wrote in message
...
Aladin,

Is SETV and GETV something new in office 2003 or is it an add-in
of some sort?? I've never seen it before and by the looks of it (ie.
the way you seem to be using it in your post) it looks pretty handy.

Dan E

"Aladin Akyurek" wrote in message
...
A couple of options with increasing performance...

1]

=IF(ISNUMBER(MATCH(A2,$E$2:$E$20,0)),VLOOKUP(A2,$E $2:$G$20,3,0),"")

2]

=IF(ISNA(SETV(VLOOKUP(A2,$E$2:$G$20,3,0))),"",GETV ())

3]

B2:

=IF(ISNA(C2),"",C2)

C2:

=VLOOKUP(A2,$E$2:$G$20,3,0)

wrote in message
...
how do i get the vlookup formula to return 0 or return
nothing instead of #n/a. i know it has something to do
with a vlookup formula nested in an "if"
statement...please advise...

thanks.
kp

What you have to do is to insert an extra check.
It would come down to something like this:
=if(isna(vlookup(A1,range,column,false)),"0",vlook up
(A1,range,column,false))

Good luck

-----Original Message-----
how do i get the vlookup formula to return 0 or return
nothing instead of #n/a. i know it has something to do
with a vlookup formula nested in an "if"
statement...please advise...

thanks.
kp
.

Thanks for the advise,

this is the formula I've entered
=IF(ISNA(VLOOKUP(Summary!B137,'2003 Rebate Aging YTD'!
$D$2:$AI$181,2,false),"0",VLOOKUP(Summary!B137,'20 03
Rebate Aging YTD'!$D$2:$AI$181,2,false))

It won't accept the "0" in between so I tried it with out
the 0 and used just "" but it won't accept that either.
Please advise...
Thanks again !

Kp




-----Original Message-----
One way:

=IF(ISNA(VLOOKUP(---),"",VLOOKUP(---))

or

=IF(COUNTIF(X,Y),VLOOKUP(---),"")

X = first column of vlookup range
Y = value to look up

HTH
Jason
Atlanta, GA

-----Original Message-----
how do i get the vlookup formula to return 0 or return
nothing instead of #n/a. i know it has something to do
with a vlookup formula nested in an "if"
statement...please advise...

thanks.
kp
.

.

" wrote...
how do i get the vlookup formula to return 0 or return
nothing instead of #n/a. i know it has something to do
with a vlookup formula nested in an "if"
statement...please advise...

One more variation. If either the row above or below the lookup table would be
blank, then the easiest and most efficient (but less robust) way to do this
would be to include that row and make the cell in the first column of that
additional row a simple reference to the lookup value. So, if your original
formula were

=VLOOKUP(A5,X21:Z100,3,0)

and X101:Z101 were blank, then enter the formula =A5 into cell X101 and change
your lookup formula to

=VLOOKUP(A5,X21:Z101,3,0)

This would return 0 if A5 had no match in X21:X100. To return "" in that case,
enter a single apostrophe in Y101 and Z101.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

Try adding an additional ")" after the first "false" in your formula:

=IF(ISNA(VLOOKUP(Summary!B137,'2003 Rebate Aging
YTD'!$D$2:$AI$181,2,false)),"0",VLOOKUP(Summary!B1 37,'2003
Rebate Aging YTD'!$D$2:$AI$181,2,false))

--
HTH,
Laura Cook
Appleton, WI


wrote in message
...
Thanks for the advise,

this is the formula I've entered
=IF(ISNA(VLOOKUP(Summary!B137,'2003 Rebate Aging YTD'!
$D$2:$AI$181,2,false),"0",VLOOKUP(Summary!B137,'20 03
Rebate Aging YTD'!$D$2:$AI$181,2,false))

It won't accept the "0" in between so I tried it with out
the 0 and used just "" but it won't accept that either.
Please advise...
Thanks again !

Kp




-----Original Message-----
One way:

=IF(ISNA(VLOOKUP(---),"",VLOOKUP(---))

or

=IF(COUNTIF(X,Y),VLOOKUP(---),"")

X = first column of vlookup range
Y = value to look up

HTH
Jason
Atlanta, GA

-----Original Message-----
how do i get the vlookup formula to return 0 or return
nothing instead of #n/a. i know it has something to do
with a vlookup formula nested in an "if"
statement...please advise...

thanks.
kp
.

.