OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Problems with Vlookup

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
lagnath
Newbie
Newbie


Joined: 12 Dec 2006
Posts: 2

PostPosted: Tue Dec 12, 2006 12:38 pm    Post subject: Problems with Vlookup Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Dec 12, 2006 12:49 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
lagnath
Newbie
Newbie


Joined: 12 Dec 2006
Posts: 2

PostPosted: Tue Dec 12, 2006 12:58 pm    Post subject: Thanks Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Dec 12, 2006 1:04 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group