| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning result as 0 from #N/A
I have created a table, using vlookup, allowing me to copy in phone stats
into an excel spreadsheet, the vlookup formula uses ID's to gather the info, in situations where the report is missing the ID the cell returns #N/A (no info error). Is there any formula that will turn the #N/A into a 0 or blank cell..? I don't want to macro the sheets for this purpose if possible.... any/ all suggestions welcome.. thanks -- JP1912 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Returning result as 0 from #N/A
try
=IF(ISNA(your_Lookup),"",your_Lookup) Mike "JP1912" wrote: I have created a table, using vlookup, allowing me to copy in phone stats into an excel spreadsheet, the vlookup formula uses ID's to gather the info, in situations where the report is missing the ID the cell returns #N/A (no info error). Is there any formula that will turn the #N/A into a 0 or blank cell..? I don't want to macro the sheets for this purpose if possible.... any/ all suggestions welcome.. thanks -- JP1912 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Returning result as 0 from #N/A
Rather than use =VLOOKUP(...)
use =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) "JP1912" wrote: I have created a table, using vlookup, allowing me to copy in phone stats into an excel spreadsheet, the vlookup formula uses ID's to gather the info, in situations where the report is missing the ID the cell returns #N/A (no info error). Is there any formula that will turn the #N/A into a 0 or blank cell..? I don't want to macro the sheets for this purpose if possible.... any/ all suggestions welcome.. thanks -- JP1912 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Returning result as 0 from #N/A
On Jun 16, 1:46*pm, JP1912 wrote:
I have created a table, using vlookup, allowing me to copy in phone stats into an excel spreadsheet, the vlookup formula uses ID's to gather the info, in situations where the report is missing the ID the cell returns #N/A (no info error). Is there any formula that will turn the #N/A into a 0 or blank cell..? *I don't want to macro the sheets for this purpose if possible.... any/ all suggestions welcome.. thanks -- JP1912 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Returning result as 0 from #N/A
Hi
=IF(ISNA(Vlookup(.....),0,Vlookup(.....)) where Vlookup(.....) is your lookup formula. Will return 0 if lookup returns #N/A. If you want to return blank, use "" instead of 0. regards Paul On Jun 16, 1:46*pm, JP1912 wrote: I have created a table, using vlookup, allowing me to copy in phone stats into an excel spreadsheet, the vlookup formula uses ID's to gather the info, in situations where the report is missing the ID the cell returns #N/A (no info error). Is there any formula that will turn the #N/A into a 0 or blank cell..? *I don't want to macro the sheets for this purpose if possible.... any/ all suggestions welcome.. thanks -- JP1912 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Returning result as 0 from #N/A
brilliant...many thanks..!!
-- JP1912 "Mike H" wrote: try =IF(ISNA(your_Lookup),"",your_Lookup) Mike "JP1912" wrote: I have created a table, using vlookup, allowing me to copy in phone stats into an excel spreadsheet, the vlookup formula uses ID's to gather the info, in situations where the report is missing the ID the cell returns #N/A (no info error). Is there any formula that will turn the #N/A into a 0 or blank cell..? I don't want to macro the sheets for this purpose if possible.... any/ all suggestions welcome.. thanks -- JP1912 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Returning result as 0 from #N/A
thanks Sam...spot on....!!
-- JP1912 "Sam Wilson" wrote: Rather than use =VLOOKUP(...) use =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) "JP1912" wrote: I have created a table, using vlookup, allowing me to copy in phone stats into an excel spreadsheet, the vlookup formula uses ID's to gather the info, in situations where the report is missing the ID the cell returns #N/A (no info error). Is there any formula that will turn the #N/A into a 0 or blank cell..? I don't want to macro the sheets for this purpose if possible.... any/ all suggestions welcome.. thanks -- JP1912 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Returning result as 0 from #N/A
Thanks Paul, got it sorted now...you learn something new every day...I know
how to "ISNA" now....;o) cheers again... -- JP1912 " wrote: Hi =IF(ISNA(Vlookup(.....),0,Vlookup(.....)) where Vlookup(.....) is your lookup formula. Will return 0 if lookup returns #N/A. If you want to return blank, use "" instead of 0. regards Paul On Jun 16, 1:46 pm, JP1912 wrote: I have created a table, using vlookup, allowing me to copy in phone stats into an excel spreadsheet, the vlookup formula uses ID's to gather the info, in situations where the report is missing the ID the cell returns #N/A (no info error). Is there any formula that will turn the #N/A into a 0 or blank cell..? I don't want to macro the sheets for this purpose if possible.... any/ all suggestions welcome.. thanks -- JP1912 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| =AVERAGE returning a #DIV/0! result | Excel Worksheet Functions | |||
| Vlookup returning more than one result | Excel Discussion (Misc queries) | |||
| Returning Forumla For Result | Excel Discussion (Misc queries) | |||
| vlookup returning a n/a result | Excel Worksheet Functions | |||
| Difficult returning result !!!! | Excel Programming | |||
Linear Mode
