Problems with Vlookup

lagnath · Newbie Joined: 12 Dec 2006 Posts: 2

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 · 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

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 · 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