The ELOOKUP Project

[Aladin Akyurek]

If you have a spreadsheet that uses VLOOKUP formulas, I'd appreciate if you can participate to the experiment described below.

Background. A while ago I formulated a proposal to extend lookup functions with an extra optional argument such that the user can set the value e.g., VLOOKUP should return in case of failure.

See if interested:

http://makeashorterlink.com/?D6ED111B1

http://makeashorterlink.com/?L11E651B1

One question that arises whether the proposed modification to the syntax would break the existing spreadsheets.

My claim is that it will not.

This can be tested with a user-defined function (UDF) that invokes the current built-in VLOOKUP, while it implements the additional optional argument. Specs for ELOOKUP can be found in:

http://makeashorterlink.com/?I552422B1

Juan Pablo has coded this in VBA (see below) for which I'm grateful. I'd like to add that Juan added this optional extension also to his UDF, called MVLOOKUP, which returns multiple records associated with a lookup value.

The experiment consists of the following:

( 1.) Make a copy of your workbook that uses VLOOKUP formulas.
( 2.) Copy the following code:

Function ELOOKUP(lookup_value As Variant, table_array As Variant, col_index_num As Integer, Optional range_lookup As Variant, Optional ValueIfError As Variant) As Variant

'
' Code: Juan Pablo G., August 2002
' Specs: Aladin Akyurek
'
    If IsMissing(range_lookup) Then range_lookup = True
    If IsMissing(ValueIfError) Then ValueIfError = CVErr(2042)

    Dim Result As Variant
    Result = Application.VLookup(lookup_value, table_array, col_index_num, range_lookup)

    If IsError(Result) Then Result = ValueIfError
    ELOOKUP = Result
End Function

( 2.) Open the copy of the workbook.
( 3.) Activate Tools|Macro|Visual Basic Editor.
( 4.) Activate Insert|Module.
( 5.) Paste the copied code in the window entitled "...(Code)".
( 6.) Activate File|Close and Return to Microsoft Excel.
( 7.) Activate Edit|Find.
( 8.) Type VLOOKUP in the box for Find what.
( 9.) Activate the Replace button.
(10.) Type ELOOKUP in the box for Replace with.
(11.) Activate the Replace All button.
(12.) Inspect whether your spreadsheet is broken anywhere after replacing VLOOKUP with ELOOKUP.
(13.) Report your findings in a follow-up post here along with the VLOOKUP formula that you use.

Note. The speed will go down, but that's not important. I'm only interested in whether an extended vlookup will break your model that uses vlookup.

Thanks.

Aladin

 

[kskinne]

i replaced my vlookups with the elookup function and it seems to work fine - i can see many great uses for this - i use vlookup extensively and I love how you can edit the function to have it return a zero or a blank on an error, this is very useful for me as with some of my files, I do not want to return an error because it will throw off my formulas. This also allows me to use much smaller formulas because I don't have nest my vlookup within an IF formula to return a blank if the vlookup errors out.

nice job guys!

kevin