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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

- 21 Nov 2003 23:15 GMT

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

Reply to this Message

Jason Morin - 21 Nov 2003 23:31 GMT

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
[quoted text clipped - 5 lines]
>kp
>.

Reply to this Message

- 12 Dec 2003 16:39 GMT

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,'2003
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:
[quoted text clipped - 23 lines]
>>
>.

Reply to this Message

Laura Cook - 21 Jan 2004 04:27 GMT

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!B137,'2003
Rebate Aging YTD'!$D$2:$AI$181,2,false))

Signature

HTH,
Laura Cook
Appleton, WI

> Thanks for the advise,
>
[quoted text clipped - 37 lines]
> >>
> >.

Reply to this Message

Aladin Akyurek - 21 Nov 2003 23:33 GMT

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)

> how do i get the vlookup formula to return 0 or return
> nothing instead of #n/a. i know it has something to do
[quoted text clipped - 3 lines]
> thanks.
> kp

Reply to this Message

Dan E - 21 Nov 2003 23:54 GMT

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

> A couple of options with increasing performance...
>
[quoted text clipped - 22 lines]
> > thanks.
> > kp

Reply to this Message

Peo Sjoblom - 22 Nov 2003 00:12 GMT

Dan,

they are from Laurent Longre's add-in Morefunc

Signature

Regards,

Peo Sjoblom

> Aladin,
>
[quoted text clipped - 30 lines]
> > > thanks.
> > > kp

Reply to this Message

Dan E - 21 Nov 2003 23:34 GMT

KP,

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

> how do i get the vlookup formula to return 0 or return
> nothing instead of #n/a. i know it has something to do
[quoted text clipped - 3 lines]
> thanks.
> kp

Reply to this Message

Wings - 22 Nov 2003 10:37 GMT

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",vlookup
(A1,range,column,false))

Good luck

>-----Original Message-----
>how do i get the vlookup formula to return 0 or return
[quoted text clipped - 5 lines]
>kp
>.

Reply to this Message

Harlan Grove - 12 Dec 2003 18:11 GMT

"anonymous@discussions.microsoft.com" 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.

Reply to this Message