This Microsoft Excel Add-In contains four useful macros. It was developed primarily to speed up large and complex spreadsheets that made lots of VLOOKUP calls. The macros are based on INDEX(MATCH()) combination. This is much quicker than VLOOKUP but it complicated and tedious to implement. Functionality has been refined over the years to only include the following four functions:
Function FVLOOKUP(ByVal vKeyValue As Variant, ByVal rgTable As Range, Optional nValueColumn As Long = 1, Optional nDefaultValue As Variant = 0) As VariantLooks for a value (vKeyValue) in the leftmost column of a table (rgTable) and then returns a value in the same row from a column you specify (nValueColumn which has a default value of 1). If no value is found then nDefaultValue (which has a default value of 0) is returned.
Function AFVLOOKUP(ByVal rgKeyValue As Range, ByVal rgTable As Range, Optional nValueColumn As Long = 1, Optional nDefaultValue As Variant = 0) As VariantLooks for values stored within a given range (rgKeyValue) in the leftmost column of a table (rgTable) and then return the SUM of the values in the same row from a column you specify (nValueColumn which has a default value of 1). If no value is found then nDefaultValue (which has a default value of 0) is returned. This is equivalent to calling FVLOOKUP for each key and then summing the results. This could be handy for instance to SUM the VLOOKUP for a range of cost centres.
Function AFVLOOKUP_PREFIX(ByVal sPrefixKeyValue As String, ByVal rgKeyValue As Range, ByVal rgTable As Range, Optional nValueColumn As Long = 1, Optional nDefaultValue As Variant = 0) As VariantLooks for values stored within a given range (rgKeyValue) but prefixed with sPrefixKeyValue in the leftmost column of a table (rgTable) and then return the SUM of the values in the same row from a column you specify (nValueColumn which has a default value of 1). If no value is found then nDefaultValue (which has a default value of 0) is returned. This is equivalent to calling FVLOOKUP for each key and then summing the results. This could be handy for instance to SUM the VLOOKUP for a range of cost centres with a given prefix such as a country code.
Function AFVLOOKUP_POSTFIX(ByVal rgKeyValue As Range, ByVal sPostKeyValue As String, ByVal rgTable As Range, Optional nValueColumn As Long = 1, Optional nDefaultValue As Variant = 0) As VariantLooks for values stored within a given range (rgKeyValue) but postfixed with sPostKeyValue in the leftmost column of a table (rgTable) and then return the SUM of the values in the same row from a column you specify (nValueColumn which has a default value of 1). If no value is found then nDefaultValue (which has a default value of 0) is returned. This is equivalent to calling FVLOOKUP for each key and then summing the results. This could be handy for instance to SUM the VLOOKUP for a range of cost centres with a given postfix such as an activity code.
“FVLOOKUP” is available free of charge.
Click here to download the latest version of FVLOOKUP.
Requirement: a working internet connection, Windows 7 or above, Excel 2013 or later and Macros must be enabled.