[mso] Fwd: RE: Re: LOOKUP function in Excel
- From: <agjones.bpd@xxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Mon, 16 May 2005 17:57:01 +0100
Hi Andrew, and thanks for the effort! I was worried that the garbled Copy/Paste
would
mean no-one would understand ..... but you have! What you say is precisely IT!
The first thing I came up with was the fact that the VECTOR (i.e. the LOOKUP
"source"
range of cells) apparently has to be sorted in Ascending order, but it isn't in
the first SS
so why does that work?!!!
Next, #NA Help, in its only reference to LOOKUP, says "If lookup_value is
smaller than
the smallest value in lookup_vector, LOOKUP gives the #N/A error value" .....
but it isn't
smaller, and the value is actually referenced FROM the vector!
I also discovered, as you say, "it just doesn't work for the final column in
the range" but
what is more, LOOKUP continues to give #NA 3 if you repeatedly increase the
column 3
value by 1 ..... UNTIL that equals and then passes the column 2 value!
Oddly enough, if you juggle the 3 single cell values, i.e. putting the one MIN
finds in one
of the other columns, then LOOKUP works but I can't rearrange the values on
every
line just to suit the whims of LOOKUP!!!!!
I need a break!
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of Andrew Kendon
Sent: 16 May 2005 16:49
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: LOOKUP function in Excel
I have to say, you have me stumped. In the second
line, if the minimum price is for Airlines #1 or #2,
it works - it just doesn't work for the final column
in the range.
Andrew Kendon
--- agjones.bpd@xxxxxxxxxxxx wrote:
> My students are doing basic work on Functions and
> two have come up with a similar
> SS formula.
>
> Anybody got any ideas why this works:
> Year United States Canada Japan France
> 1984 7.5 10.9 2.8 10 1.7
>
> =MAX(B5:H5) =LOOKUP(I5,$B$5:$H$5,$B$3:$H$3)
>
> ..... But this doesn't:
>
>
>
> towns Airline 1 Airline 2 Airline 3 cheapest best
> airline
> New York 325 330 355 =MIN(B2:D2) =LOOKUP
> (E2,B2:D2,$B$1:$D$1)
> Berlin 167 159 86 =MIN(B3:D3) =LOOKUP
> (E3,B3:D3,$B$1:$D$1)
>
>
> towns Airline 1 Airline 2 Airline 3 cheapest best
> airline
> New York £325 £330 £355 £325 Airline 1
> Berlin £167 £159 £86 £86 #N/A
>
>
>
--------------------------------------------------------------
> 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
>
*************************************************************
>
___________________________________________________________
How much free photo storage do you get? Store your holiday
snaps for FREE with Yahoo! Photos http://uk.photos.yahoo.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
*************************************************************
----- 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:
