[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
lagnath
Newbie

Joined: 12 Dec 2006
Posts: 2

 Posted: Tue Dec 12, 2006 12:38 pm    Post subject: Problems with Vlookup I'm trying to use Calc in place of Excel in my workplace and I've got a problem getting Vlookup to behave in Calc in the same way it does in Excel. My situation is this. I've got two sets of data and i'm using Vlookup to compare one Column of data to another similar Column of data, then getting latitude and longitude numbers off of the second sheet and onto the first. The setup is. Sheet A has a set of identifier numbers and will be getting the new data. Sheet B has identifier numbers as well as latitude and longitude coords. I need to use the each of the elements in the Identifer Column in Sheet A and IF it has a match in Sheet B copy the Latitude and Longitude from sheet B over to Sheet A. The Pseudo Code for Vlookup is should look like this VLOOKUP(Number to be Compared;Range of Rows and Colums containing data;Index of Element to Return) My identifer is in column C and i'm using the following formula and then copying and pasting it into the range of cells that I want to have Lats and Longs (which will be in column A and B). =VLOOKUP(C1;'Well Spots'.\$D\$1:\$N\$1037;14) this formula will then be copied from call A1 all the way to the bottom. In excel, this exact same formula (minus the style changes between the two programs) turns into the latitude I need. When I use this formula in Calc I get Err:502. I've looked up both the function and the error, and I can't figure out why this won't compare the Data in cell C1 to that in D1-D1037 and return the 14th element from the left. Any assistance would be greately appreciated.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Tue Dec 12, 2006 12:49 pm    Post subject: The Pseudo Code for Vlookup is should look like this (in both applications): VLOOKUP(ANYVALUE to be Compared;Range of Rows and Colums containing data;Index of Element to Return;[get minimal match from sorted list]) Unfortunately the last parameter defaults to 1 (TRUE) if it is missing. So your formula should read: =VLOOKUP(C1;'Well Spots'.\$D\$1:\$N\$1037;14,0) If the last param is missing or 1 the search range needs to be sorted ascending by the first column and 0 A 1 B 2 C 3 D 4 E 5 F VLOOKUP(2.5 ; A1:B6 ; 2) returns 2_________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
lagnath
Newbie

Joined: 12 Dec 2006
Posts: 2

 Posted: Tue Dec 12, 2006 12:58 pm    Post subject: Thanks I realized my mistake came in two parts. Leaving out the piece you specifed and additionally, one of the columns doesn't aparently come out of the raw data I get it from as a properly formatted number. The function to convert text to a number is FIXED() in both applications correct? Don't know how I left that part out before. Nothing like posting on a forum to make your obvious mistakes apparent.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Tue Dec 12, 2006 1:04 pm    Post subject: No, Text2Number is VALUE("123.5") which fails if locale uses a different decimal separator. VALUE("1/12/2007") may fail due to differences between british and US styles of dates. Convert to number in place: Select the values in question Menu:Edit>Find/Replace Search: .* Replace: & More Options: [x]Current Selection only [x]Use Regular Expressions_________________Rest in peace, oooforum.org Get help on http://forum.openoffice.org
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum