 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / August 2007Array
Hi, Back again with another problem. Using xl 2004 version 11.3.5 The formula below entered as an array(Jik being my data) returns the Closest match. Please what would I change in the formula to get not the closest but the data value that is preceding my Target value E.G Data 100,200,300,400,500. Target value entered 361 returns 400. If I enter a target value between 300 and 400 would like the array to Return 300. Hope this is clear. Thanks Paul =INDEX(Jik,MATCH(SMALL(ABS(Target-Jik),1),ABS(Target-Jik),0)) On 8/29/07 10:07 PM, in article C2FC636A.11AE%asking@optusnet.com.au, "Skin Paul" <asking@optusnet.com.au> wrote: > Hi, Back again with another problem. Using xl 2004 version 11.3.5 > The formula below entered as an array(Jik being my data) returns the [quoted text clipped - 6 lines] > > =INDEX(Jik,MATCH(SMALL(ABS(Target-Jik),1),ABS(Target-Jik),0)) Look in Excel Help. You want to change the 0 as the last parameter in Match to -1, but you'll need to resort your data.  SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom Hi, Thanks for the reply bob. The reason I resorted to an array was that I did not want to sort my data. I had been using Vlookup successfully but Sorting was a hassle. So I thought I would try something new. Thanks again. Paul On 30/8/07 11:40 PM, in article C2FC4116.9C620%bob@nospam.com, "Bob Greenblatt" <bob@nospam.com> wrote: > On 8/29/07 10:07 PM, in article C2FC636A.11AE%asking@optusnet.com.au, "Skin > Paul" <asking@optusnet.com.au> wrote: [quoted text clipped - 12 lines] > Look in Excel Help. You want to change the 0 as the last parameter in Match > to -1, but you'll need to resort your data. You don't have to sort the data to use VLOOKUP if you set the optional argument (Range_lookup) to FALSE, forcing it to look for an exact match. SignatureRegards |:>) Bob Jones [MVP] Office:Mac > Hi, Thanks for the reply bob. The reason I resorted to an array was that I > did not want to sort my data. I had been using Vlookup successfully but [quoted text clipped - 22 lines] >> Match >> to -1, but you'll need to resort your data. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.