software

I am using a VLookup function. I need to find an exact match, but when there
is no match, I would like to see a zero, rather than #N/A. Can someone help
me with a different formula to produce similar results as I am getting with
the VLookup without the #N/A?

=if(iserror(vlookup(...)),0,vlookup(....))
Arla wrote:
gt;
gt; I am using a VLookup function. I need to find an exact match, but when there
gt; is no match, I would like to see a zero, rather than #N/A. Can someone help
gt; me with a different formula to produce similar results as I am getting with
gt; the VLookup without the #N/A?

--

Dave Peterson

I am sorry if I am being dense; so now that I have added the quot;iserrorquot;
portion to the formula, do I need to have quot;vlookupquot; in two spots rather than
just once?

quot;Dave Petersonquot; wrote:

gt; =if(iserror(vlookup(...)),0,vlookup(....))
gt;
gt;
gt;
gt; Arla wrote:
gt; gt;
gt; gt; I am using a VLookup function. I need to find an exact match, but when there
gt; gt; is no match, I would like to see a zero, rather than #N/A. Can someone help
gt; gt; me with a different formula to produce similar results as I am getting with
gt; gt; the VLookup without the #N/A?
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Yes, once to check if the VLOOKUP returns an error, once to get the result
if it doesn't.

--

HTH

RP

quot;Arlaquot; gt; wrote in message
...
gt; I am sorry if I am being dense; so now that I have added the quot;iserrorquot;
gt; portion to the formula, do I need to have quot;vlookupquot; in two spots rather
than
gt; just once?
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; =if(iserror(vlookup(...)),0,vlookup(....))
gt; gt;
gt; gt;
gt; gt;
gt; gt; Arla wrote:
gt; gt; gt;
gt; gt; gt; I am using a VLookup function. I need to find an exact match, but
when there
gt; gt; gt; is no match, I would like to see a zero, rather than #N/A. Can
someone help
gt; gt; gt; me with a different formula to produce similar results as I am getting
with
gt; gt; gt; the VLookup without the #N/A?
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;
Actually, not true. If you use the IFERROR function rather than
IF(ISERROR()), then you can do this:

=IFERROR(VLOOKUP(), 0)

This will return the result of the VLOOKUP if the VLOOKUP is successful but
will return the second parameter (in this case, 0), if the VLOOKUP is
unsuccessful.

This is definitely a more efficient approach than the IF(ISERROR())
construction since the VLOOKUP is performed only once.

-Charlesquot;Bob Phillipsquot; wrote:

gt; Yes, once to check if the VLOOKUP returns an error, once to get the result
gt; if it doesn't.
gt;
gt; --
gt;
gt; HTH
gt;
gt; RP
gt;
gt; quot;Arlaquot; gt; wrote in message
gt; ...
gt; gt; I am sorry if I am being dense; so now that I have added the quot;iserrorquot;
gt; gt; portion to the formula, do I need to have quot;vlookupquot; in two spots rather
gt; than
gt; gt; just once?
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; =if(iserror(vlookup(...)),0,vlookup(....))
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Arla wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; I am using a VLookup function. I need to find an exact match, but
gt; when there
gt; gt; gt; gt; is no match, I would like to see a zero, rather than #N/A. Can
gt; someone help
gt; gt; gt; gt; me with a different formula to produce similar results as I am getting
gt; with
gt; gt; gt; gt; the VLookup without the #N/A?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt;
gt;

=iferror() was added in xl2007, though.

Charles Moore wrote:
gt;
gt; Actually, not true. If you use the IFERROR function rather than
gt; IF(ISERROR()), then you can do this:
gt;
gt; =IFERROR(VLOOKUP(), 0)
gt;
gt; This will return the result of the VLOOKUP if the VLOOKUP is successful but
gt; will return the second parameter (in this case, 0), if the VLOOKUP is
gt; unsuccessful.
gt;
gt; This is definitely a more efficient approach than the IF(ISERROR())
gt; construction since the VLOOKUP is performed only once.
gt;
gt; -Charles
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Yes, once to check if the VLOOKUP returns an error, once to get the result
gt; gt; if it doesn't.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; RP
gt; gt;
gt; gt; quot;Arlaquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I am sorry if I am being dense; so now that I have added the quot;iserrorquot;
gt; gt; gt; portion to the formula, do I need to have quot;vlookupquot; in two spots rather
gt; gt; than
gt; gt; gt; just once?
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =if(iserror(vlookup(...)),0,vlookup(....))
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Arla wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am using a VLookup function. I need to find an exact match, but
gt; gt; when there
gt; gt; gt; gt; gt; is no match, I would like to see a zero, rather than #N/A. Can
gt; gt; someone help
gt; gt; gt; gt; gt; me with a different formula to produce similar results as I am getting
gt; gt; with
gt; gt; gt; gt; gt; the VLookup without the #N/A?
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

--

Dave Peterson

When VLookup finds a match, it returns the Lookup_Value itself. Is there a
way to return the position in the Table_array where VLookup found the match?

quot;Charles Moorequot; wrote:

gt; Actually, not true. If you use the IFERROR function rather than
gt; IF(ISERROR()), then you can do this:
gt;
gt; =IFERROR(VLOOKUP(), 0)
gt;
gt; This will return the result of the VLOOKUP if the VLOOKUP is successful but
gt; will return the second parameter (in this case, 0), if the VLOOKUP is
gt; unsuccessful.
gt;
gt; This is definitely a more efficient approach than the IF(ISERROR())
gt; construction since the VLOOKUP is performed only once.
gt;
gt; -Charles
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Yes, once to check if the VLOOKUP returns an error, once to get the result
gt; gt; if it doesn't.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; RP
gt; gt;
gt; gt; quot;Arlaquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I am sorry if I am being dense; so now that I have added the quot;iserrorquot;
gt; gt; gt; portion to the formula, do I need to have quot;vlookupquot; in two spots rather
gt; gt; than
gt; gt; gt; just once?
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =if(iserror(vlookup(...)),0,vlookup(....))
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Arla wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am using a VLookup function. I need to find an exact match, but
gt; gt; when there
gt; gt; gt; gt; gt; is no match, I would like to see a zero, rather than #N/A. Can
gt; gt; someone help
gt; gt; gt; gt; gt; me with a different formula to produce similar results as I am getting
gt; gt; with
gt; gt; gt; gt; gt; the VLookup without the #N/A?
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

Use MATCH instead of VLOOKUP.

Hope this helps.

Pete

On Jun 22, 2:37*pm, Rick F lt;Rick gt;
wrote:
gt; When VLookup finds a match, it returns the Lookup_Value itself. Is there a
gt; way to return the position in the Table_array where VLookup found the match?
gt;
gt;
gt;
gt; quot;Charles Moorequot; wrote:
gt; gt; Actually, not true. If you use the IFERROR function rather than
gt; gt; IF(ISERROR()), then you can do this:
gt;
gt; gt; =IFERROR(VLOOKUP(), 0)
gt;
gt; gt; This will return the result of the VLOOKUP if the VLOOKUP is successful but
gt; gt; will return the second parameter (in this case, 0), if the VLOOKUP is
gt; gt; unsuccessful.
gt;
gt; gt; This is definitely a more efficient approach than the IF(ISERROR())
gt; gt; construction since the VLOOKUP is performed only once.
gt;
gt; gt; -Charles
gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; gt; Yes, once to check if the VLOOKUP returns an error, once to get the result
gt; gt; gt; if it doesn't.
gt;
gt; gt; gt; --
gt;
gt; gt; gt; HTH
gt;
gt; gt; gt; RP
gt;
gt; gt; gt; quot;Arlaquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; gt; I am sorry if I am being dense; so now that I have added the quot;iserrorquot;
gt; gt; gt; gt; portion to the formula, do I need to have quot;vlookupquot; in two spots rather
gt; gt; gt; than
gt; gt; gt; gt; just once?
gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; gt; gt; gt; =if(iserror(vlookup(...)),0,vlookup(....))
gt;
gt; gt; gt; gt; gt; Arla wrote:
gt;
gt; gt; gt; gt; gt; gt; I am using a VLookup function. *I need to find an exact match, but
gt; gt; gt; when there
gt; gt; gt; gt; gt; gt; is no match, I would like to see a zero, rather than #N/A. *Can
gt; gt; gt; someone help
gt; gt; gt; gt; gt; gt; me with a different formula to produce similar results as I am getting
gt; gt; gt; with
gt; gt; gt; gt; gt; gt; the VLookup without the #N/A?
gt;
gt; gt; gt; gt; gt; --
gt;
gt; gt; gt; gt; gt; Dave Peterson- Hide quoted text -
gt;
gt; - Show quoted text -=if(iserror(vlookup(...)),0,vlookup(....))

is a really good solution to the problem. I am using this formular a lot. I
just found out that it is possible to create formulars with Microsoft Visual
Basic. I would like to create an easier version of the above mentioned
formular,
something like
=evlookup(..) for if iserror vlookup?

I dont have a lot of experience with MVB, does anybody know how to create
this?

Thanks Cornelius
Charles -- that works perfectly in my application; way more efficient.

Thanks,
Drew

quot;Charles Moorequot; wrote:

gt; Actually, not true. If you use the IFERROR function rather than
gt; IF(ISERROR()), then you can do this:
gt;
gt; =IFERROR(VLOOKUP(), 0)
gt;
gt; This will return the result of the VLOOKUP if the VLOOKUP is successful but
gt; will return the second parameter (in this case, 0), if the VLOOKUP is
gt; unsuccessful.
gt;
gt; This is definitely a more efficient approach than the IF(ISERROR())
gt; construction since the VLOOKUP is performed only once.
gt;
gt; -Charles
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Yes, once to check if the VLOOKUP returns an error, once to get the result
gt; gt; if it doesn't.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; RP
gt; gt;
gt; gt; quot;Arlaquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I am sorry if I am being dense; so now that I have added the quot;iserrorquot;
gt; gt; gt; portion to the formula, do I need to have quot;vlookupquot; in two spots rather
gt; gt; than
gt; gt; gt; just once?
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =if(iserror(vlookup(...)),0,vlookup(....))
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Arla wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am using a VLookup function. I need to find an exact match, but
gt; gt; when there
gt; gt; gt; gt; gt; is no match, I would like to see a zero, rather than #N/A. Can
gt; gt; someone help
gt; gt; gt; gt; gt; me with a different formula to produce similar results as I am getting
gt; gt; with
gt; gt; gt; gt; gt; the VLookup without the #N/A?
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;