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

IF and Vlookup, Excel to Cacl OOo

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


Joined: 07 Jan 2005
Posts: 1
Location: Bedfordshire, UK

PostPosted: Fri Jan 07, 2005 7:14 am    Post subject: IF and Vlookup, Excel to Cacl OOo Reply with quote

We are moving from Excel to OOo Calc. We have spreadsheets which are written on Excel 97. Unfortunately for various reasons we will continue to use and create formula in Excel for some time. I have to ensure they also work in Calc.

The cell formula in question is below.

Original Excel Version

=IF(VLOOKUP($A2,'Update Address'!$A:$Z,2,FALSE)=0,
IF(VLOOKUP($A2,Address!$A:$Z,2,FALSE)=0, "",
VLOOKUP($A2,Address!$A:$Z,2,FALSE)),
VLOOKUP($A2,'Update Address'!$A:$Z,2,FALSE))

This will look at a update_address sheet, if blank go to address sheet and return the result.

When read in Calc it comes in as;

=IF(VLOOKUP($A2;$Update_Address.$A$1:$Z$32000;2;FALSE())=0;
IF(VLOOKUP($A2;$Address.$A$1:$Z$32000;2;FALSE())=0;"";
VLOOKUP($A2;$Address.$A$1:$Z$32000;2;FALSE()));
VLOOKUP($A2;$Update_Address.$A$1:$Z$32000;2;FALSE()))

Which does not work. It only returns the update_address entry.

The following does work.

=IF(VLOOKUP($A2;$Update_Address.$A$1:$Z$32000;2;0)="";
IF(VLOOKUP($A2;$Address.$A$1:$Z$32000;2;0)=0;"";
VLOOKUP($A2;$Address.$A$1:$Z$32000;2;0));
VLOOKUP($A2;$Update_Address.$A$1:$Z$32000;2;0))

The problem seems to be the FALSE() statement. This is required in Excel or the Vlookup will return the best fit match not the exact match.

Is there a formula that can be created in Excel that will also work in Calc?

Thanks,

Stuart
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