Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP - return 0 instead of "#N/A"
When using VLOOKUP to find an exact match for a value, and if there is no
exact match, the formula will return "#N/A". How should I modify the formula to make it return the number 0 instead? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP - return 0 instead of "#N/A"
=if(iserror(vlookup(...)),0,vlookup(...))
In xl2007: =iferror(vlookup(...),0) EDCNB wrote: When using VLOOKUP to find an exact match for a value, and if there is no exact match, the formula will return "#N/A". How should I modify the formula to make it return the number 0 instead? Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP - return 0 instead of "#N/A"
Nest it with an IF with a test for the #NA condition like this
=IF(ISNA(VLOOKUP(A1,range,col,param),0,VLOOKUP(A1, range,col,param)) What that says is test if the lookup will cause #NA, and if it will then display 0 (zero) else go ahead and perform the VLOOKUP for real and display its result. The zero doesn't even have to be a zero, in other conditions you could put a custom phrase there such as ,"No Match Found", "EDCNB" wrote: When using VLOOKUP to find an exact match for a value, and if there is no exact match, the formula will return "#N/A". How should I modify the formula to make it return the number 0 instead? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP - return 0 instead of "#N/A"
=IF(ISERROR(VLOOKUP([your criteria])),0,VLOOKUP([your criteria]))
"IF the VLOOKUP returns an error, THEN 0, ELSE do the VLOOKUP." Dave -- Brevity is the soul of wit. "EDCNB" wrote: When using VLOOKUP to find an exact match for a value, and if there is no exact match, the formula will return "#N/A". How should I modify the formula to make it return the number 0 instead? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup, return zero if not found | Excel Worksheet Functions | |||
vlookup function return all values | Excel Worksheet Functions | |||
Using a Vlookup to return values in a data list? | Excel Worksheet Functions | |||
Want VLookup to Return the row above | Excel Worksheet Functions | |||
Vlookup of an if statement return | Excel Worksheet Functions |