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

[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

 

[Jason Morin]

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

 

[Aladin Akyurek]

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)

 

[Dan E]

KP,

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

Dan E

 

[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

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

 

[Peo Sjoblom]

Dan,

they are from Laurent Longre's add-in Morefunc


--

Regards,

Peo Sjoblom

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

 

[Wings]

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

 

[Guest]

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

 

[Harlan Grove]

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.

 

[Laura Cook]

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