Lookup not working, appears to be finding the nearest match ??

[Peter Richards]

Hi,

I'm trying to use a LOOKUP function, to lookup values in another
worksheet (same Excel file). The data being used for the lookup has a
worksheet name of "Mobile_Numbers", and basically looks like this

509280913 Fred
512337837 Bill
519326561 Mary
519897853 Matthew
521921931 Alice
525348717 Andrew
527733723 Steve

The numbers are range A3:A9 and the person is range B3:B9

If I am on row 150 of another spreadsheet, and the mobile number is in
cell F150, then the formulae is as follows:

=LOOKUP(F150,Mobile_Numbers!A3:A9,Mobile_Numbers!B3:B9)

What is happening is as follows:

1. If there is an exact match, then the cell/info being returned is
correct

2. If there is NOT a macth, then it appears that in some cases, the
value being returned is the 'closest' value in range B3:B9 of the
other worksheet, but not always ??

Obviously if there is no match, I just want no data returned, an empty
cell.

I've used LOOKUP before, and can't figure out what is the problem.
I've checked the format of the cells, to make sure they are the
same,ect,etc, but can't figure out what the problem is ??

It is Excel 9.0.3821 SR-1, it cale with Ofiice 2000 Developer.

Thanks,

Peter

 

[Ragdyer]

When you use Lookup, you cannot stipulate an *exact* match!
Simply switch to Vlookup, which accepts arguments that it return *only*
exact matches.

This formula will return *exact* matches only.
If there is no match, the #N/A error will be returned, alerting you to that
fact.

=VLOOKUP(F150,Mobile_Numbers!A3:B9,2,0)

 

[Dave Peterson]

Try =vlookup():

=VLOOKUP(F150,Mobile_Numbers!A3:A9,2,false)

 

[Peter Richards]

Hi Dave & "RD",

Thanks, that works just fine, no doubt passing FALSE is the same as
pasing a '0'.

Now, there is just one minor thing with pasting the formulaue. Is
there any method to paste, so that the VLOOKUP value changes (relative
from where I copied), but all the other values remain the same ??

If I copy row 136 to row 137, I would like the paste to be:

=VLOOKUP(F137,Mobile_Numbers!A3:B9,2,0)

but I cannot get it to do this, only this .............

=VLOOKUP(F137,Mobile_Numbers!A4:B10,2,0)

Thanks,

Peter

 

[Peter Richards]

Dave,

Are you the same Dave Pearson from the comp.lang.clipper newsgroup ??

Peter

 

[Peo Sjoblom]

Use

=VLOOKUP(F137,Mobile_Numbers!$A$3:$B$9,2,0)



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Peter Richards]

Hi Peo,

Thanks, that did it, works great.

Peter

 

[Dave Peterson]

Nope. I'm a Peterson--not a Pearson. (which delights most Pearson's!)

(and sorry about not noticing the colunm adjustment!)