Lookup Function Problem

[seve]

I am using lookup function properly (I think).

Problem: If the lookup value is in the lookup vector no problem.

But if the lookup value is not in the lookup vector...it gives a lookup
result... HOW?

Any help?


Steve

[RagDyeR]

If Lookup() can't find the exact lookup value, it matches the largest value
in the lookup vector, that is less than or equal to the lookup value.

That's how come you're receiving a return.

You should perhaps be using Vlookup() or the Index() and Match()
combination.

Post back with your present formula if you would care for some suggestions,
using the other functions.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"seve" <[email protected]> wrote in message
news:[email protected]...
I am using lookup function properly (I think).

Problem: If the lookup value is in the lookup vector no problem.

But if the lookup value is not in the lookup vector...it gives a lookup
result... HOW?

Any help?


Steve

[seve]

RD,

Thanks for you advice, I was in the process of investigating vlookup
and finally made it work.

Many thanks for your advice.

Steve

[Ragdyer]

You're welcome!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"seve" <[email protected]> wrote in message
news:[email protected]...
> RD,
>
> Thanks for you advice, I was in the process of investigating vlookup
> and finally made it work.
>
> Many thanks for your advice.
>
> Steve
>

[Aladin Akyurek]

There is no reason to abondon LOOKUP if the lookup table is in ascending
order...

seve wrote:
> RD,
>
> Thanks for you advice, I was in the process of investigating vlookup
> and finally made it work.
>
> Many thanks for your advice.
>
> Steve
>

[Ragdyer]

OP is looking for an exact match Aladin, as construed in the third line of
the OP.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> There is no reason to abondon LOOKUP if the lookup table is in ascending
> order...
>
> seve wrote:
> > RD,
> >
> > Thanks for you advice, I was in the process of investigating vlookup
> > and finally made it work.
> >
> > Many thanks for your advice.
> >
> > Steve
> >

[Aladin Akyurek]

=IF(LOOKUP(LookupValue,$F$2:$F$100)=LookupValue,LOOKUP(LookupValue,$F$2:$G$100),"")

where F2:G100 is the lookup table, sorted on F2:F100 in ascending order,
will perform better than:

=VLOOKUP(LookupValue,$F$2:$G$100,2,0)

or

=IF(ISNA(VLOOKUP(LookupValue,$F$2:$G$100,2,0)),"",VLOOKUP(LookupValue,$F$2:$G$100,2,0))

Ragdyer wrote:
> OP is looking for an exact match Aladin, as construed in the third line of
> the OP.

[Ragdyer]

Could you define "performs better"?

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.

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?

Is the performance noticable in a 100 row by 50 column datalist? ... 500 X
100?

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.

A complex VBA suggestion in the "New Users" group, to an OP with no apparent
knowledge of the "fill handle" or absolute or relative references comes to
mind.

True, oft times the discussion is removed from the OP, and continues among
the respondents, for their own edification and entertainment and "one
upsmanship".

Is that what this is?<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
>
=IF(LOOKUP(LookupValue,$F$2:$F$100)=LookupValue,LOOKUP(LookupValue,$F$2:$G$1
00),"")
>
> where F2:G100 is the lookup table, sorted on F2:F100 in ascending order,
> will perform better than:
>
> =VLOOKUP(LookupValue,$F$2:$G$100,2,0)
>
> or
>
>
=IF(ISNA(VLOOKUP(LookupValue,$F$2:$G$100,2,0)),"",VLOOKUP(LookupValue,$F$2:$
G$100,2,0))
>
> Ragdyer wrote:
> > OP is looking for an exact match Aladin, as construed in the third line
of
> > the OP.

[Aladin Akyurek]

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.

[...]

[Ragdyer]

<"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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> [...]