 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006lookup tables
ok, i have a value, 15.086, which i want to look up in a table of values which are all integers, shown below. 10 12 18 22 33 Mathematically, the closest value is 18. However, when i use VLOOKUP it tells me that 12 is the closest, because it searches down the list, and not up. Is there a way of searching up the list, or both up and down, in order to find the true closest value? Regards, Phil Newman A bit long winded but =IF(ISNA(VLOOKUP(A3,$E$1:$E$5,1,0)),INDEX($E$1:$E$5,MATCH(A3,$E$1:$E$5,1)+1),VLOOKUP(A3,$E$1:$E$5,1,0)) The value to lookup (15.086) is in A1 and the table of values in E1:E5 The INDEX/MATCH does the advancing by 1 but we need to avoid this if an exact match is found. best wishes  SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > ok, > [quoted text clipped - 17 lines] > > Phil Newman blimey, that IS long-winded! but it works great, so thanks very much! Phil > A bit long winded but > =IF(ISNA(VLOOKUP(A3,$E$1:$E$5,1,0)),INDEX($E$1:$E$5,MATCH(A3,$E$1:$E$5,1)+1),VLOOKUP(A3,$E$1:$E$5,1,0)) [quoted text clipped - 28 lines] > > > > Phil Newman actually, that doesn't work too great, because it now only chooses to go up, rather than decide which is closer... for example, i have 33.8, and the values are 33 and 39. it now chooses 39 as the closest! Phil > blimey, that IS long-winded! but it works great, so thanks very much! > [quoted text clipped - 32 lines] > > > > > > Phil Newman Phil, here's one which returns the minimum difference between the two closest values; =MIN(I1-INDEX(H1:H5,MATCH(I1,H1:H5)),INDEX(H1:H5,MATCH(I1,H1:H5)+1)-I1) with your integers in H1:H5 and the value you are trying to match in I5. I've not sussed out yet how to convert this back into one of the integers - maybe another cell with an IF in it ... Hope this helps for now ... Pete > actually, that doesn't work too great, because it now only chooses to > go up, rather than decide which is closer... [quoted text clipped - 40 lines] > > > > > > > > Phil Newman Phil, I needed a few hours' break to get something to eat - I see you've had some other responses. Here's a non-array formula in one cell that returns the closest integer from your table (assumed to be H1:H5 as before, with the number to be matched in i5. If your number lies exactly midway between 2 integers, this returns the lower integer: =IF(VLOOKUP(I1,H$1:H$5,1)+MIN(I1-VLOOKUP(I1,H$1:H$5,1),INDEX(H$1:H$5,MATCH(I1,H$1:H$5)+1)-I1)=I1,VLOOKUP(I1,H$1:H$5,1),INDEX(H$1:H$5,MATCH(I1,H$1:H$5)+1)) Vlookup is used for "normal" match (closest, lower) and INDEX/MATCH for the next higher number. Hope this helps. Pete > Phil, > [quoted text clipped - 55 lines] > > > > > > > > > > Phil Newman But you did not ask for that! What you want when the test-value is exactly midway? Say 15, which is 3 away from 12, and 3 away from 18. Do you want the larger or the smaller answer? Can you cope with a User Defined Function in VBA if I make one up? See David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm best wishes SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > actually, that doesn't work too great, because it now only chooses to > go up, rather than decide which is closer... [quoted text clipped - 42 lines] >> > > >> > > Phil Newman quote: "Is there a way of searching up the list, or both up and down, in order to find the true closest value?" in the unlikely case where the non-integer value lies exactly between the two integers, the larger integer will suffice. yes, i can cope with a user-defined function, that would be very nice thank you. Regards, > But you did not ask for that! > What you want when the test-value is exactly midway? Say 15, which is 3 away [quoted text clipped - 55 lines] > >> > > > >> > > Phil Newman Hi Phil Try the array entered formula {=INDEX(A1:A5,MATCH(MIN(ABS($B$1-A1:A5)),ABS(($B$1-A1:A5)),-1))} To enter or edit and array formula, use Ctrl+Shift+Enter (CSE) instead of Enter. Do not type the curly braces { } yourself, Excel will enter then when you use CSE. SignatureRegards Roger Govier > actually, that doesn't work too great, because it now only chooses to > go up, rather than decide which is closer... [quoted text clipped - 47 lines] >> > > >> > > Phil Newman Thanks, that seems to work well. how come i need to edit the array with CSE? Phil > Hi Phil > [quoted text clipped - 61 lines] > >> > > > >> > > Phil Newman Hi Phil All array formulae must use Ctrl+Shift+Enter when initially created, or when edited, otherwise Excel treats them as standard formulae, and will not work upon each of the elements in an array manner. SignatureRegards Roger Govier > Thanks, > [quoted text clipped - 76 lines] >> >> > > >> >> > > Phil Newman This is very clever. When B1 is 17, the inner part becomes =MATCH(1,{7;5;1;5;16},-1) But the array is not in descending order as specified by Help. I suppose this does not matter since the match is found before the number start to increase? Any comment? SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > Hi Phil > [quoted text clipped - 53 lines] >>> > > >>> > > Phil Newman Hi Bernard The array being tested is in ascending order in the OP supplied and in my test. Therefore, the ABS() of the difference between the test and the array is descending at first and then begins to rise. As you rightly say, because we are looking for the MIN, that gets found before the numbers begin to rise again, hence the solution seems to work with the -1 parameter. I cannot get it to work with 0, or with 1. It sometimes works with 1, if the value being tested is integer, but not with a decimal value. Whilst in the true sense, it does not follow the rule for Index, because of the Min and Abs it seems to work. Having just written the above, I did just create another list with numbers in random order, and again the formula works with -1 but fails with either 0 or 1. I am now at a loss to explain why it works, other than as far as my testing seems to be concerned - it does!!!! SignatureRegards Roger Govier > This is very clever. > When B1 is 17, the inner part becomes =MATCH(1,{7;5;1;5;16},-1) [quoted text clipped - 64 lines] >>>> > > >>>> > > Phil Newman This works for me with *or* without a sorted data list: =INDEX(A1:A5,MATCH(MIN(ABS(A1:A5-B1)),ABS(A1:A5-B1),0)) SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Hi Bernard > [quoted text clipped - 82 lines] >>>>> > > >>>>> > > Phil Newman Hi RD I have just re-tested my data and I agree it works both ways with 0 as the parameter. Lots of blushes - I didn't press CSE when I switched from -1 to 0!!!!! SignatureRegards Roger Govier > This works for me with *or* without a sorted data list: > [quoted text clipped - 89 lines] >>>>>> > > >>>>>> > > Phil Newman But, what I can't seem to get, is what the OP mentioned as a preference ... namely ... the *higher* value when the lookup value is *exactly between* the integers in the datalist. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Hi RD > [quoted text clipped - 95 lines] >>>>>>> > > >>>>>>> > > Phil Newman I should mention ... In a *sorted* list! Of course, unsorted, if the larger value is displayed first, it will be the returned value. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > But, what I can't seem to get, is what the OP mentioned as a preference > ... namely ... the *higher* value when the lookup value is *exactly [quoted text clipped - 99 lines] >>>>>>>> > > >>>>>>>> > > Phil Newman Hi RD Now I recall why I was trying -1 rather than 0. With data of 10,12,18,22,33 in A1:A5 and with 20 entered in B1 With -1 it returns 22 as the result (what the OP wanted) With 0 it returns 18 as the result. SignatureRegards Roger Govier > But, what I can't seem to get, is what the OP mentioned as a > preference ... namely ... the *higher* value when the lookup value is [quoted text clipped - 101 lines] >>>>>>>> > > >>>>>>>> > > Phil Newman It doesn't do that for me! SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Hi RD > [quoted text clipped - 106 lines] >>>>>>>>> > > >>>>>>>>> > > Phil Newman I would think that using 0 (for an exact match) instead of -1 would be safer. > Hi Phil > [quoted text clipped - 61 lines] > >> > > > >> > > Phil Newman SignatureDave Peterson Hi Dave I don't think we could use 0, as it is unlikely that an exact match will ever occur. SignatureRegards Roger Govier >I would think that using 0 (for an exact match) instead of -1 would be >safer. [quoted text clipped - 71 lines] >> >> > > >> >> > > Phil Newman Hi Dave Retract that. I'm being an idiot (yet again !!!) Of course the difference is bound to exist in the list of differences. I was thinking of the data value being looked up. Apologies. SignatureRegards Roger Govier > Hi Dave > [quoted text clipped - 77 lines] >>> >> > > >>> >> > > Phil Newman I thought that maybe you came across one of those 15 significant digit rounding differences that reared its ugly head when min() was added to abs(). > Hi Dave > [quoted text clipped - 98 lines] > >> > >> Dave Peterson SignatureDave Peterson Hi Dave No, just me being very tired at the end of a long day. Better get off to bed and get some sleep, then maybe I will start thinking clearly (again?) SignatureRegards Roger Govier >I thought that maybe you came across one of those 15 significant digit >rounding [quoted text clipped - 112 lines] >> >> >> >> Dave Peterson How about this array formula: =MAX(IF(ABS($A$1:$A$5-$B$1)=MIN(ABS($A$1:$A$5-$B$1)),$A$1:$A$5)) (I didn't read/notice the requirement to choose the largest value that was closest to the given value.) > Hi Dave > [quoted text clipped - 127 lines] > > > > Dave Peterson SignatureDave Peterson Hi RD and Dave I just don't believe it. This morning, using -1 does not produce the same result. Max - it behaves as you say Dave - Your formula cracks the problem. Me - I'm off to the Opticians!!! SignatureRegards Roger Govier > How about this array formula: > [quoted text clipped - 143 lines] >> > >> > Dave Peterson Thanks very much! all the formulas work very well, i think i'll use the last one (it's shorter!) and seems to do what i want it to, which is great. thanks for all your help. Phil > How about this array formula: > [quoted text clipped - 134 lines] > > > > > > Dave Peterson Hi Phil Thanks for the feedback (appreciated by all). I'm sorry the whole thread got so protracted through my inadequacies!! SignatureRegards Roger Govier > Thanks very much! all the formulas work very well, i think i'll use > the [quoted text clipped - 157 lines] >> > > >> > > Dave Peterson But it got resolved by your tenaciousness. > Hi Phil > [quoted text clipped - 171 lines] > >> > >> Dave Peterson SignatureDave Peterson Isn't that tenacity? <bg> Pete > But it got resolved by your tenaciousness. > [quoted text clipped - 173 lines] > > >> > > >> Dave Peterson I had to check my spelling first (just in case the CQC (Candian Quality Council) was lurking! tenacious tenacious (te-nâ´shes) adjective 1. Holding or tending to hold persistently to something, such as a point of view. See synonyms at STRONG. 2. Holding together firmly; cohesive: a tenacious material. 3. Clinging to another object or surface; adhesive: tenacious lint on my jacket. 4. Tending to retain; retentive: a tenacious memory. [From Latin tenâx, tenâc-, holding fast, from tenêre, to hold.] - tena´ciously adverb - tena´ciousness noun The American Heritage® Dictionary of the English Language, Third Edition copyright © 1992 by Houghton Mifflin Company. Electronic version licensed from InfoSoft International, Inc. All rights reserved. (Office 95 came with Bookshelf 95!) > Isn't that tenacity? <bg> > [quoted text clipped - 181 lines] > > > > Dave Peterson SignatureDave Peterson Yes, it's in my dictionary, too, though yours doesn't list "tenacity" - this seems more natural to us Brits. I should have realised that the Google Maestro would always be able to find a reference!! <bg> Anyway, whichever, I think it is an attribute which most regular posters exhibit. Pete > I had to check my spelling first (just in case the CQC (Candian Quality Council) > was lurking! [quoted text clipped - 202 lines] > > > > > > Dave Peterson Tenacity was in a different "branch" of the output. And this came from a Bookshelf 95--a program that was included in office95--before MS came up with Encarta (IIRC). But heck, has anything changed in the world since 1995??? > Yes, it's in my dictionary, too, though yours doesn't list "tenacity" - > this seems more natural to us Brits. I should have realised that the [quoted text clipped - 215 lines] > > > > Dave Peterson SignatureDave Peterson |
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.