Re: Lookup Function Problem

From: Ragdyer (RagDyer_at_cutoutmsn.com)
Date: 02/28/05 

Date: Sun, 27 Feb 2005 18:46:21 -0800

<"On the other hand, correctness, robustness, and efficiency should be of
concern too. Perhaps more so.">

There is no discussion as to the "correctness" point.
That goes without saying.

As to the other two, they both are *definitely* relative !

A year or two ago, you posted some test times between the Index-Match
combination versus the double (error checking) Vlookup function.
That post made me change an enormous database WB over to the Index-Match
combo, saving almost 50% in the time of opening and re-calc time.
That was my first *personal* experience with formula efficiency, which I owe
to you.
BUT ... you must admit, that the majority of the WBs that are created and
revised out of the answers OPs receive here , in these groups, are far from
any significant size to really warrant a *major* concern as to efficiency.
(I hope Harlan doesn't read this. He's always harping in lessening function
calls.)
How many times are there questions pertaining to sport pools, card clubs,
and small businesses.

I do feel that the major concern of suggestions posted here should be to
enhance the understanding of the OP, where the sphere of knowledge of the OP
*must* be taken into consideration.

With no formal computer education, I would gauge that 75% of what I know
about XL has come from these NGs, with the balance coming from reading a
"QUE" Excel 5.0 book, and the experience of making a department run
exclusively on XL.
So I vividly remember what it means to read a suggestion and not have the
foggiest notion as to how to revise it to fit my situation.

So, correctness absolutely first, but efficiency should take a back seat to
simplicity to enhance the understanding of the individual poster. 

-- 
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Aladin Akyurek" <akyurek@xs4all.nl> wrote in message
news:422275c0$0$28976$e4fe514c@news.xs4all.nl...
> Ragdyer wrote:
> > Could you define "performs better"?
>
> Means more efficient.
>
> >
> > I don't doubt you when it comes to anything technical, but I would
venture a
> > guess that the vast majority of the respondents within these groups
wouldn't
> > have come up with your formula if they were given a stipulation of
> > suggesting a procedure to return an exact match from a datalist.
>
> The OP indicated having a sorted data area/table. Lookup functions are
> always faster with such tables. That is:
>
> =VLOOKUP(LookupValue,Table,ColIdx,1)
>
> =INDEX(ReturnRange,MATCH(LookupValue,MatchRange,1))
>
> =LOOKUP(LookupValue,Table)
>
> The latter does not know better.
>
>
> >
> > If you'll notice, in my original response to the OP, I suggested the
Index
> > and Match combination in addition toVlookup.
> > Does that combo also "perform less better" then the formula you
suggested?
> >
>
> The issue is: What is the match-type? 0 or 1 - if you will, FALSE or
> TRUE? Index/Match with match-type set to 1 will perform equally as Lookup.
>
> > Is the performance noticable in a 100 row by 50 column datalist? ... 500
X
> > 100?
> >
>
> I think the answer is yes.
>
> > I truly believe that the simplicity of the suggestions in a comparison
to
> > the estimated expertise of the OP, gauged by the question itself,
dictates
> > how complex the answer should (could ... would) be.
> >
>
> I can imagine the position you take. On the other hand, correctness,
> robustness, and efficiency should be of concern too. Perhaps more so.
>
> [...]

Relevant Pages

  • Re: Just for the record
    ... the problem (correctness, efficiency, etc.) while bearing in mind that ... I hope you won't mind if add a tangential comment. ... sufficient attention to either relevance or correctness. ... While playing with the query, I happen to notice that SELECT ...
    (comp.databases.theory)
  • Re: Howto get workgroup name
    ... That always exist struggle between correctness and efficiency, ... WMI won't help a bit because it will require to authenticate too. ... answer unauthenticated queries is simply opening a security hole. ...
    (microsoft.public.win32.programmer.networks)
  • Re: Can you learn computer science from a school?
    ... about correctness from the concern about not using quadratic ... Are the gains from separating these two concerns sufficient ... depends upon what kind of efficiency is possible; ... the more important bits of prior knowledge. ...
    (comp.lang.lisp)
  • Re: Writing bulletproof code
    ... >Sometimes correctness is paramount. ... >as storing real variables in 8 bytes) in the interests of efficiency. ... at the end of the day we're only setting pixels on ... Who cares if that Laura is a pixel out? ...
    (comp.programming)
Loading