[mso] FW: Re: LOOKUP function in Excel
- From: <agjones.bpd@xxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Wed, 25 May 2005 15:16:56 +0100
Hi Ray
Does that not extend to being able to specify the nth, in which case it should
find all?
Or am I missing the point somewhere?
:-)
Graham
----- Forwarded message from Graham Jones <agjones@xxxxxxxxxxxxxx> -----
Date: Wed, 25 May 2005 13:39:24 +0100
From: Graham Jones <agjones@xxxxxxxxxxxxxx>
Reply-To: Graham Jones <agjones@xxxxxxxxxxxxxx>
Subject: FW: [mso] Re: LOOKUP function in Excel
To: "'agjones.bpd@xxxxxxxxxxxx'" <agjones.bpd@xxxxxxxxxxxx>
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of Ray Blake
Sent: 25 May 2005 12:58
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: LOOKUP function in Excel
Sorry, I was away for a few days, so have only just received this.
Yes, the first match thing is pretty much a fixed restricition of all LOOKUP
functions, although you can look or - say - the 2nd or 3rd occurrence
specifically if you need to.
Ray
------------------------------------
GR Business Process Solutions
Ray Blake
Head of Software Design
ray@xxxxxxxxx
Braedon
Newell Road
Hemel Hempstead
Herts HP3 9PD
tel: 01442 396518
fax: 01442 389353
mobile: 07834 226601
www.grbps.com
------------------------------------
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of Mike Andrew
Sent: 18 May 2005 16:12
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: LOOKUP function in Excel
Ray - Thanks for the reference to the article on nesting an INDEX within = a
Match formula. Very useful.
Do you know how to modifying this technique to apply to columns of =
repeated values? The formula appears to stop after the first match is =
found.
Thanks in anticipation
Mike
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of Ray Blake
Sent: 16 May 2005 19:04
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Fwd: RE: Re: LOOKUP function in Excel
Thanks for the vote of confidence, Andrew!
I'm always very reluctant to use the LOOKUP formula, because unlike VLOOKUP
and HLOOKUP it doesn't have the final argument which allows you to say - in
effect - "These values may not be in order, so look at all of them to get an
exact match" Unfortunately, VLOOKUP won't let you get a result to the left
of your range, and HLOOKUP won't let you get a result above it.=20
When I want a reliable result in these circumstances, I nest an INDEX within
a MATCH formula. There's an article here showing how it works:
http://www.grbps.com/Excel6.pdf
Ray
------------------------------------
GR Business Process Solutions
Ray Blake
Head of Software Design
ray@xxxxxxxxx
Braedon
Newell Road
Hemel Hempstead
Herts HP3 9PD
tel: 01442 396518
fax: 01442 389353
mobile: 07834 226601
www.grbps.com
------------------------------------
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of Andrew Kendon
Sent: 16 May 2005 18:36
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Fwd: RE: Re: LOOKUP function in Excel
In case it helps your researches, I also added a spoof 4th column with a
value which wouldn't show in MIN ... Same result. If the minimum value is
not in columns 1 or 2 (regardless of the order) then LOOKUP doesn't appear
to work. Incidentally, that was in Office 2000. Now I'm home, I'm going to
try it in 2003.
For problems like this, Ray Blake is usually your man: I hope he has some
input :)
You deserve a break, lol!
Andrew K =20
> -----Original Message-----
> From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
>Behalf Of=20 agjones.bpd@xxxxxxxxxxxx
> Sent: 16 May 2005 17:57
> To: mso@xxxxxxxxxxxxx
> Subject: [mso] Fwd: RE: Re: LOOKUP function in Excel
>=20
> Hi Andrew, and thanks for the effort! I was worried that the
> garbled Copy/Paste would mean no-one would understand .....=20
> but you have! What you say is precisely IT!
>=20
> The first thing I came up with was the fact that the VECTOR
> (i.e. the LOOKUP "source"=20
> range of cells) apparently has to be sorted in Ascending=20
> order, but it isn't in the first SS so why does that work?!!!
>=20
> Next, #NA Help, in its only reference to LOOKUP, says "If
> lookup_value is smaller than the smallest value in=20
> lookup_vector, LOOKUP gives the #N/A error value" ..... but=20
> it isn't smaller, and the value is actually referenced FROM=20
> the vector!
>=20
> I also discovered, as you say, "it just doesn't work for the
> final column in the range" but what is more, LOOKUP continues=20
> to give #NA 3 if you repeatedly increase the column 3 value=20
> by 1 ..... UNTIL that equals and then passes the column 2 value!
>=20
> Oddly enough, if you juggle the 3 single cell values, i.e.
> putting the one MIN finds in one of the other columns, then=20
> LOOKUP works but I can't rearrange the values on every line=20
> just to suit the whims of LOOKUP!!!!!
>=20
> I need a break!
>=20
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx = or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to=20
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the =
quotes) in the subject line.
Or, visit the group's homepage and use the dropdown menu. This will = also
allow you to change your email settings to digest or vacation (no = mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, = send
a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an =
invitation with instructions. Once you are a member of the files group, =
you can go here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the
quotes) in the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions. Once you are a member of the files group, you can go
here to upload/download files: http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes)
in the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions. Once you are a member of the files group, you can go
here to upload/download files: http://www.smartgroups.com/vault/msofiles
*************************************************************
----- End forwarded message -----
--------------------------------------------------------------
Sent with "Me-Mail", Boltblue's FREE mobile messaging service.
http://www.boltblue.com
----- End forwarded message -----
--------------------------------------------------------------
Sent with "Me-Mail", Boltblue's FREE mobile messaging service.
http://www.boltblue.com
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in
the subject line.
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
