IF and Vlookup, Excel to Cacl OOo

[sdaniels]

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