Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I create a formula to retun a value of 0 (zero) when a VLO.
When searching multiple tables with a V or HLOOKUP formula, I want a search
that doe not find anything to result in a zero instead of an error (#N/A) I have tried a variations of IF formulas but am missing something. Thanks |
#2
|
|||
|
|||
IF(ISNA(YourFormula),0,YourFormula)
-- Kind Regards, Niek Otten Microsoft MVP - Excel "Adam at GTOFL" <Adam at wrote in message ... When searching multiple tables with a V or HLOOKUP formula, I want a search that doe not find anything to result in a zero instead of an error (#N/A) I have tried a variations of IF formulas but am missing something. Thanks |
#3
|
|||
|
|||
You could also use this formula:
=LOOKUPV(C1,A1:B100,2,0,0) or =LOOKUPV(C1,A1:B100,2,0,"") But first 1. Press Alt+F11. Insert Module. Copy and Paste the below: Function LookupV(Lookup_Value, Table_Array As Range, Col_Index_Num, Range_value, Optional Error_Msg) LookupV = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num, Range_value) If IsError(LookupV) And Not IsMissing(Error_Msg) Then LookupV = Error_Msg End Function Make sure the VBA code is only 4 rows! The LOOKUPV formula is Shorter and is Faster then VLOOKUP Ola Sandstrom |
#4
|
|||
|
|||
Do you mean it's faster than the 2 calls that are used in a formula like
=IF(ISNA(VLOOKUP(....)),0,VLOOKUP(...)) That could be, but LOOKUPV(...) certainly can't be faster than =VLOOKUP(...) On Sat, 19 Feb 2005 11:21:02 -0800, Ola wrote: You could also use this formula: =LOOKUPV(C1,A1:B100,2,0,0) or =LOOKUPV(C1,A1:B100,2,0,"") But first 1. Press Alt+F11. Insert Module. Copy and Paste the below: Function LookupV(Lookup_Value, Table_Array As Range, Col_Index_Num, Range_value, Optional Error_Msg) LookupV = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num, Range_value) If IsError(LookupV) And Not IsMissing(Error_Msg) Then LookupV = Error_Msg End Function Make sure the VBA code is only 4 rows! The LOOKUPV formula is Shorter and is Faster then VLOOKUP Ola Sandstrom |
#5
|
|||
|
|||
You are right Myrna, VBA code is always slower. So VLOOKUP should always be
used when Error handling is not needed. However in this case, I've done some 20.000 rows test and LOOKUPV has been faster, since it only does the VLOOKUP procedure once not twice. So the main reson for this post is to offer an alternative - which I think work. But do test it. Ola Sandstrom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a formula that would allow me to subtract from a d | New Users to Excel | |||
Create formula | Excel Worksheet Functions | |||
create a chart with a formula | Charts and Charting in Excel | |||
Create a formula that determines a date-sensitive File-Name from w | Excel Worksheet Functions | |||
How do I create a formula in Excel that will countif or sumif bef. | Excel Worksheet Functions |