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 *************************************************************