ExcelBanter - #N/A in VLookup - Can I use another function that will return 0?

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - #N/A in VLookup - Can I use another function that will return 0? (https://www.excelbanter.com/excel-worksheet-functions/65702-n-vlookup-can-i-use-another-function-will-return-0-a.html)

Arla

#N/A in VLookup - Can I use another function that will return 0?

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?

Dave Peterson

#N/A in VLookup - Can I use another function that will return 0?

=if(iserror(vlookup(...)),0,vlookup(....))

Arla wrote:

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?

--

Dave Peterson

Arla

#N/A in VLookup - Can I use another function that will return

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

"Dave Peterson" wrote:

=if(iserror(vlookup(...)),0,vlookup(....))

Arla wrote:

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?

--

Dave Peterson

Bob Phillips

#N/A in VLookup - Can I use another function that will return

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

--

HTH

RP

"Arla" wrote in message
...
I am sorry if I am being dense; so now that I have added the "iserror"
portion to the formula, do I need to have "vlookup" in two spots rather
than
just once?

"Dave Peterson" wrote:

=if(iserror(vlookup(...)),0,vlookup(....))

Arla wrote:

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?

--

Dave Peterson

Charles Moore

#N/A in VLookup - Can I use another function that will return

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.

-Charles

"Bob Phillips" wrote:

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

--

HTH

RP

"Arla" wrote in message
...
I am sorry if I am being dense; so now that I have added the "iserror"
portion to the formula, do I need to have "vlookup" in two spots rather
than
just once?

"Dave Peterson" wrote:

=if(iserror(vlookup(...)),0,vlookup(....))

Arla wrote:

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?

--

Dave Peterson

Dave Peterson

#N/A in VLookup - Can I use another function that will return

=iferror() was added in xl2007, though.

Charles Moore wrote:

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.

-Charles

"Bob Phillips" wrote:

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

--

HTH

RP

"Arla" wrote in message
...
I am sorry if I am being dense; so now that I have added the "iserror"
portion to the formula, do I need to have "vlookup" in two spots rather
than
just once?

"Dave Peterson" wrote:

=if(iserror(vlookup(...)),0,vlookup(....))

Arla wrote:

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?

--

Dave Peterson

--

Dave Peterson

Rick F

#N/A in VLookup - Can I use another function that will return

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?

"Charles Moore" wrote:

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.

-Charles

"Bob Phillips" wrote:

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

--

HTH

RP

"Arla" wrote in message
...
I am sorry if I am being dense; so now that I have added the "iserror"
portion to the formula, do I need to have "vlookup" in two spots rather
than
just once?

"Dave Peterson" wrote:

=if(iserror(vlookup(...)),0,vlookup(....))

Arla wrote:

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?

--

Dave Peterson

Pete_UK

#N/A in VLookup - Can I use another function that will return

Use MATCH instead of VLOOKUP.

Hope this helps.

Pete

On Jun 22, 2:37*pm, Rick F <Rick
wrote:
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?

"Charles Moore" wrote:
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.

-Charles

"Bob Phillips" wrote:

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

--

HTH

RP

"Arla" wrote in message
...
I am sorry if I am being dense; so now that I have added the "iserror"
portion to the formula, do I need to have "vlookup" in two spots rather
than
just once?

"Dave Peterson" wrote:

=if(iserror(vlookup(...)),0,vlookup(....))

Arla wrote:

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?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Cornelius

#N/A in VLookup - Can I use another function that will return

=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

Gills

#N/A in VLookup - Can I use another function that will return

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

Thanks,
Drew

"Charles Moore" wrote:

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.

-Charles

"Bob Phillips" wrote:

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

--

HTH

RP

"Arla" wrote in message
...
I am sorry if I am being dense; so now that I have added the "iserror"
portion to the formula, do I need to have "vlookup" in two spots rather
than
just once?

"Dave Peterson" wrote:

=if(iserror(vlookup(...)),0,vlookup(....))

Arla wrote:

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?

--

Dave Peterson

All times are GMT +1. The time now is 04:09 AM.