| View previous topic :: View next topic |
| Author |
Message |
JakeBonfield
Newbie
Joined: 10 Feb 2012
Posts: 4
|
 Posted: Fri Feb 10, 2012 4:02 pm Post subject: Using semicolon within functions [solved] |
 |
|
I'm relatively new to OpenOffice as I use MS Office 2007 at work, however I've recently been playing with OpenOffice at home. I sent a spreadsheet from work to home, but I have been having some trouble with OpenOffice using semi-colons as function seperators.
I am unable to get a Vlookup function to work with an array that consists of two non adjacent columns. I believe this is because the semi colon used to define the array will move the function on to the next argument.
If I try to prevent this issue by surrounding the array argument in brackets, the semicolon automatically changes to a tilde symbol (~) and returns the error "Err:504".
Does anybody know if there is a way I can get this formula to work, or if this is not possible with OpenOffice? I've tried searching online, but can't seem to find anything. I've included a simplified example below.
A 11
B 22
C 33
D 44
=VLOOKUP(33;A1:A4;B1:B4;1) #too many semi colons
=VLOOKUP(33;(A1:A4~B1:B4);1) #returns "Err:504"
Thanks,
Jake
Last edited by JakeBonfield on Sat Feb 11, 2012 7:40 am; edited 2 times in total |
|
| Back to top |
|
 |
ozzie
OOo Advocate
Joined: 29 Jul 2010
Posts: 330
Location: victoria
|
| Posted: Fri Feb 10, 2012 7:14 pm Post subject: |
|
|
I am not an expert, but does this do what you want
| Code: | | =VLOOKUP(33;A1:B4;2;0) |
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
JakeBonfield
Newbie
Joined: 10 Feb 2012
Posts: 4
|
| Posted: Sat Feb 11, 2012 4:54 am Post subject: |
|
|
In the spreadsheet I'm working on, the two columns are not next to each other.
Last edited by JakeBonfield on Sat Feb 11, 2012 6:04 am; edited 1 time in total |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1874
Location: Sydney, Australia
|
| Posted: Sat Feb 11, 2012 5:50 am Post subject: |
|
|
| JakeBonfield wrote: | | In the spreadsheet I'm working on, the two rows are not next to each other. |
You seem to be confusing rows with columns.
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
JakeBonfield
Newbie
Joined: 10 Feb 2012
Posts: 4
|
| Posted: Sat Feb 11, 2012 6:07 am Post subject: |
|
|
| Sorry, my mistake, I've corrected my other posts. I'll try to blame that on making the posts late at night and when I woke up... |
|
| Back to top |
|
|
range
General User
Joined: 04 Jan 2012
Posts: 21
|
| Posted: Sat Feb 11, 2012 6:13 am Post subject: |
|
|
| I don't think VLOOKUP() allows non-contiguous ranges, but you can include the non-relevant columns in your range and use the INDEX parameter to read the correct column. |
|
| Back to top |
|
|
JakeBonfield
Newbie
Joined: 10 Feb 2012
Posts: 4
|
| Posted: Sat Feb 11, 2012 7:40 am Post subject: |
|
|
| range wrote: | | I don't think VLOOKUP() allows non-contiguous ranges, but you can include the non-relevant columns in your range and use the INDEX parameter to read the correct column. |
Thank you, I hadn't thought of just including the other columns. I'll mark this as solved, but out of curiousity, do you know why the semicolon was changed to a ~ automatically? |
|
| Back to top |
|
|
gerard24
OOo Enthusiast
Joined: 08 Jul 2011
Posts: 100
Location: France
|
|
| Back to top |
|
|
|
