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

 

[lenze]

Hi Aladin:

End of my day, so I tried it

I replaced my Vlookup in my worksheet with

=IF(ISERROR(ELOOKUP(A4,$L$4:$L$44,1,0)),"","OK")

Everything functioned as before. Not sure though if my error trap doesn't defeat your purpose.

HTH
This message was edited by lenze on 2002-09-04 14:56

 

[Aladin Akyurek]

On 2002-09-04 14:54, lenze wrote:
Hi Aladin:

End of my day, so I tried it

I replaced my Vlookup in my worksheet with

=IF(ISERROR(ELOOKUP(A4,$L$4:$L$44,1,0)),"","OK")

Everything functioned as before. Not sure though if my error trap doesn't defeat your purpose.

HTH
This message was edited by lenze on 2002-09-04 14:56

That's OK. The hypothetical here is that when MS decides to embrace the proposal at some future date, we'll know that the already existing spreadsheets will not be affected adversely by the new VLOOKUP.

Thanks for testing and report.

 

[Juan Pablo González]

Lenze, you replaced VLOOKUP with that entire formula ? or just with ELOOKUP ? I think that would be the first step. Replace all instances of VLOOKUP with ELOOKUP to see if they break (Wich they shouldn't). Then, start to add the last, optional parameter, to handle unwanted #N/A's.

 

[Juan Pablo González]

Another thing, speed SHOULDN'T go down THAT much, because I'm using the builtin VLOOKUP, and only doing some simple tests in there. So, the difference shouldn't be that much noticeable (Altough I don't have the numbers to prove it...)

Edit: Ok, I take my words back... the difference is noticeable. With 10,000 rows the average calculation time for VLOOKUP was 0.083329 and for ELOOKUP was 2.127843.

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-09-04 16:14

 

[RichardS]

Aladin

I thought I would have a really good file to test on, but they are mostly HLOOKUP formulae. Replaced VLOOKUP's anyway, and no issues.

eg

replaced

=vlookup(C1,'F:EXCELDTAColeraine[Lookups.xls]Sheet1'!$A$2:$B$13,2)

with

=elookup(C1,'F:EXCELDTAColeraine[Lookups.xls]Sheet1'!$A$2:$B$13,2)

Richard

 

[PaddyD]

works inside an array entered formula:

=SUMPRODUCT(IF(C1>=A2:A6,A2:A6)-IF(C1>=A2:A6,A1:A5),B1:B5)+(C1-VLOOKUP(C1,A1:A6,1))*VLOOKUP(C1,A1:B6,2)

Paddy

 

[lenze]

On 2002-09-04 16:03, Juan Pablo G. wrote:
Lenze, you replaced VLOOKUP with that entire formula ? or just with ELOOKUP ? I think that would be the first step. Replace all instances of VLOOKUP with ELOOKUP to see if they break (Wich they shouldn't). Then, start to add the last, optional parameter, to handle unwanted #N/A's.

Aladin and Juan
OK:Here's what I did.

My Original formula was
=IF(ISERROR(VLOOKUP(A4,$L$4:$L$44,1,0)),"","OK") which I changed to
=IF(ISERROR(ELOOKUP(A4,$L$4:$L$44,1,0)),"","OK") This functioned correctly. I now have modified the formula as such
=IF(ELOOKUP(A5,$L$4:$L$44,1,0,1),"","OK")

This works and DOES suppress the Errors.

Good job guys!!!

 

[Jay Petrulis]

On 2002-09-04 16:06, Juan Pablo G. wrote:
Another thing, speed SHOULDN'T go down THAT much, because I'm using the builtin VLOOKUP, and only doing some simple tests in there. So, the difference shouldn't be that much noticeable (Altough I don't have the numbers to prove it...)

Edit: Ok, I take my words back... the difference is noticeable. With 10,000 rows the average calculation time for VLOOKUP was 0.083329 and for ELOOKUP was 2.127843.

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-09-04 16:14

Hi Juan Pablo,

Nice job. I didn't test the following, but if you set a default to handle missing arguments, you can do away with two of the tests...<pre>Function ELOOKUP(lookup_value As Variant, table_array As Variant, _
col_index_num As Integer, Optional range_lookup As Boolean = True, _
Optional ValueIfError As Variant = CVErr(2042)) As Variant

Dim Result As Variant
'
' Code: Juan Pablo G., August 2002
' Specs: Aladin Akyurek
'
Result = _
Application.VLookup(lookup_value, table_array, col_index_num, range_lookup)

If IsError(Result) Then Result = ValueIfError
ELOOKUP = Result
End Function</pre>

My first thought was the same as yours -- the UDF would be only marginally slower, but your test does away with that a bit. Possibly this might improve the speed a little?

How about data typing? Would restricting certain of the arguments to ranges speed it up? In any event, that would reduce the functionality, so I'm not sure that the tradeoff is worth it and I agree with Aladin that the speed is not important if the end goal is to send it off to MS as a wish list item.

 

[Juan Pablo González]

Jay, thanks for your answers.

First, the Optional Variable as Type = Predefined value is a very good option. I create all my UDF's like that, BUT, here's why I made the call to not include them in there.

If you use the function as it is, in the Paste function wizard you'll see the first three arguments in bold font, meaning they're required, and the last two in regular font, meaning they're optional, right ? well, if you add the = Predefined value part to the optional arguments, they appear in BOLD ! Why ? beats me, very strange.

Two, variable declaration. I too had the range_lookup as a Boolean, but, since VLOOKUP can accept any value (Well, kind of) as the last parameter, and works accordingly, not only TRUE or FALSE, i went the same way... The other ones HAVE to be variant, because you can receive (Or give) all sorts of data, not only a specific kind.