| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
#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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I use the vlookup function to return a sum of the values? | Excel Discussion (Misc queries) | |||
| VLOOKUP Function | Excel Discussion (Misc queries) | |||
| Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
| Array Function with VLOOKUP | Excel Worksheet Functions | |||
| Vlookup w/Date Function | Excel Worksheet Functions | |||
Hybrid Mode
