 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / August 2007LOOKUP returning some incorrect values
I have a problem that has appeared in a large spreadsheet where the Lookup function returns some values correct and not others. To test what was happening I did a small spreadsheet to simply match two sets of numbers. it works for about half the column down to G17 and then goes crazy. It does get the last result correct! The result column should just be a list of descending numbers - the reverse of the data columns . The test data is below: Result G1 1 G34 34 G2 2 G33 33 G3 3 G32 32 G4 4 G31 31 G5 5 G30 30 G6 6 G29 29 G7 7 G28 28 G8 8 G27 27 G9 9 G26 26 G10 10 G25 25 G11 11 G24 24 G12 12 G23 23 G13 13 G22 22 G14 14 G21 21 G15 15 G20 20 G16 16 G19 19 G17 17 G18 18 G18 18 G17 17 G19 19 G16 1 G20 20 G15 1 G21 21 G14 1 G22 22 G13 1 G23 23 G12 1 G24 24 G11 1 G25 25 G10 1 G26 26 G9 34 G27 27 G8 34 G28 28 G7 34 G29 29 G6 34 G30 30 G5 34 G31 31 G4 34 G32 32 G3 29 G33 33 G2 19 G34 34 G1 1 I am sure it is something simple but it is driving me crazy - any suggestions? Thanks  Signaturejeff What would have helped more than your *results* would be a copy of the *formula* that is returning them, but lacking that let me guess - Does the lookup range (table) in the formula use absolute references, i.e.: $G1$:$G$45 as opposed to just using relative references: G1:G45 If not, the relative references are changing as the formula is copied down & soon runs out of the table range. SignatureHTH |:>) Bob Jones [MVP] Office:Mac >I have a problem that has appeared in a large spreadsheet where the Lookup > function returns some values correct and not others. To test what was [quoted text clipped - 44 lines] > suggestions? > Thanks Oops, yes i should have included the formula. Yes it does use absolute references as follows: =LOOKUP(D3,$A$3:$A$36,$B$3:$B$36) Signaturejeff > What would have helped more than your *results* would be a copy of the > *formula* that is returning them, but lacking that let me guess - Does the [quoted text clipped - 57 lines] > > suggestions? > > Thanks > Oops, yes i should have included the formula. Yes it does use absolute > references as follows: =LOOKUP(D3,$A$3:$A$36,$B$3:$B$36) [quoted text clipped - 69 lines] > > > -- > > > jeff Jeff, I think if you replace G1 with G01, (etc thru G9), it will work correctly. What puzzles me is that I would have thought that the wrong answers would have begun with G10 rather than G16. Maybe there's some hexidecimal conversion silently working in the background. jpdphd > > Oops, yes i should have included the formula. Yes it does use absolute > > references as follows: =LOOKUP(D3,$A$3:$A$36,$B$3:$B$36) [quoted text clipped - 75 lines] > hexidecimal conversion silently working in the background. > jpdphd Thanks for that, it does fix the problem. I thought it was something to do with G11 having two ones, as this was where it was tripping up in the main spreadsheet although it got past this in the test spreadsheet. I don't know why this works but i can live with it - thanks again. Jeff Hi Jeff - If you happen to return this may help explain it: Since you're dealing with a text string each character has a value associated with it and Excel looks at the series of values, not the string as a whole - IOW, when you look at G11 you see "Gee eleven", when Excel looks at the same string it sees "Gee one one". Anything with a higher single digit after the G (such as G2) is interpreted as a higher value than any where the first digit after the G is a 1 (G1, G11, G111, ad infinitum). If you're interested there is a table of ascii values at: http://www.asciitable.com/ HTH |:>) Bob Jones [MVP] Office:Mac On 8/17/07 5:53 PM, in article 0C331E74-0CF0-415D-92C5-DBB550FD2BAC@microsoft.com, "jeff" Jeff, It is working ABSOLUTELY correctly, and as expected. What is happening is that your input array, the stuff in A3:A36 is not is ascending order as REQUIRED by the lookup function. You think they are in order, but they aren't. To do what you want, the values in column A must be G01, G02, or G 1 G 2, etc. the lookup function is returning the correct matching value for the next lower value. Read the Help discussion carefully. If you do not want to change the values in A3:A11, use the index and match functions: =INDEX($B$3:$B$36,MATCH(D3,$A$3:$A$36,0)) SignatureBob Greenblatt [MVP], Macintosh bobgreenblattATmsnDOTcom Yep, thanks all, I can see now how it thinks - problem solved! Signaturejeff > Jeff, > [quoted text clipped - 7 lines] > If you do not want to change the values in A3:A11, use the index and match > functions: =INDEX($B$3:$B$36,MATCH(D3,$A$3:$A$36,0)) |
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
|
©2009 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.