Code required for VLookup returning #NA
 

Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a column
which is then totalled using sum or subtotal, but these formulas won't work
because of the #N/As returned.
Any help woul dbe much appreceiated.

Code required for VLookup returning #NA
 

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.

Code required for VLookup returning #NA
 

I've tried this but for some reason it's not liked, it doesn't like the value
I'm trying to return if NA (which is 0, so nothing too complicated), could I
be doing something very wrong, this is the formula as I've typed it..
=IF(ISNA(VLOOKUP($E7,'[lead gen campaign cumulative january by source
code.xls]Sheet1'!$A:$G,3,FALSE),0,VLOOKUP($E7,'[lead gen campaign cumulative
january by source code.xls]Sheet1'!$A:$G,3,FALSE)

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.

Code required for VLookup returning #NA
 

Sorry, don't know if you can still help, I've amended the formula from my
last reply to this (there was a close bracket missing): it now appears to
work but returns the wrong value:
=IF(ISNA(VLOOKUP($E7,'[lead gen campaign cumulative january by source
code.xls]Sheet1'!$A:$G,3,FALSE)),0,VLOOKUP($E7,'[lead gen campaign cumulative
january by source code.xls]Sheet1'!$A:$G,3,FALSE))

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.

Code required for VLookup returning #NA
 

Ignore all previous, i#ve corrected my stupid mistakes and now it all works
perfectly, thanks so much.

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.

Code required for VLookup returning #NA
 

Make sure that calculation is set to automatic--just in case.
Tools|options|calculation tab

But then look at your data once more. I bet you'll find that match earlier than
you think--maybe on a hidden row???

Marie Bayes wrote:

Sorry, don't know if you can still help, I've amended the formula from my
last reply to this (there was a close bracket missing): it now appears to
work but returns the wrong value:
=IF(ISNA(VLOOKUP($E7,'[lead gen campaign cumulative january by source
code.xls]Sheet1'!$A:$G,3,FALSE)),0,VLOOKUP($E7,'[lead gen campaign cumulative
january by source code.xls]Sheet1'!$A:$G,3,FALSE))

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.




--

Dave Peterson

Code required for VLookup returning #NA
 

Thanks Dave, as you might have noticed, all was present and correct and it
was just me making a mistake!

"Dave Peterson" wrote:

Make sure that calculation is set to automatic--just in case.
Tools|options|calculation tab

But then look at your data once more. I bet you'll find that match earlier than
you think--maybe on a hidden row???

Marie Bayes wrote:

Sorry, don't know if you can still help, I've amended the formula from my
last reply to this (there was a close bracket missing): it now appears to
work but returns the wrong value:
=IF(ISNA(VLOOKUP($E7,'[lead gen campaign cumulative january by source
code.xls]Sheet1'!$A:$G,3,FALSE)),0,VLOOKUP($E7,'[lead gen campaign cumulative
january by source code.xls]Sheet1'!$A:$G,3,FALSE))

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.




--

Dave Peterson

Code required for VLookup returning #NA
 

Your other post hadn't arrived through the internets to me--I think the tubes
were clogged!

Marie Bayes wrote:

Thanks Dave, as you might have noticed, all was present and correct and it
was just me making a mistake!

"Dave Peterson" wrote:

Make sure that calculation is set to automatic--just in case.
Tools|options|calculation tab

But then look at your data once more. I bet you'll find that match earlier than
you think--maybe on a hidden row???

Marie Bayes wrote:

Sorry, don't know if you can still help, I've amended the formula from my
last reply to this (there was a close bracket missing): it now appears to
work but returns the wrong value:
=IF(ISNA(VLOOKUP($E7,'[lead gen campaign cumulative january by source
code.xls]Sheet1'!$A:$G,3,FALSE)),0,VLOOKUP($E7,'[lead gen campaign cumulative
january by source code.xls]Sheet1'!$A:$G,3,FALSE))

"Jon Peltier" wrote:

Marie -

Try something like this:

=IF(ISNA(<lookup formula),<value to return if N/A,<lookup formula)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Marie Bayes" wrote in message
...
Hi Super clever coders
I would love to have some VB code that I could put into a spreadsheet (or
another clever way to do this) that will return a 0 whenever a VLOOKUP
formula returns a vaue of #N/A. I use VLOOPUP to return values into a
column
which is then totalled using sum or subtotal, but these formulas won't
work
because of the #N/As returned.
Any help woul dbe much appreceiated.




--

Dave Peterson


--

Dave Peterson