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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)
 
KP,

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

Dan E
 
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 said:
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
with a vlookup formula nested in an "if"
statement...please advise...

thanks.
kp
Click to expand...
Click to expand...
 
Dan,

they are from Laurent Longre's add-in Morefunc


--

Regards,

Peo Sjoblom

Dan E said:
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...

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
with a vlookup formula nested in an "if"
statement...please advise...

thanks.
kp
Click to expand...
Click to expand...
Click to expand...
 
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
 
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
 
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...
Click to expand...

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.
 
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))
 
Back
Top