[mso] Re: LOOKUP function in Excel
- From: "Mike Andrew" <mike.andrew@xxxxxxxxxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Wed, 18 May 2005 16:11:32 +0100
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
*************************************************************
Other related posts:
