Excel 2007 : OFFSET Match formula need help

hi all,
I am building SS in 2007 and require it to work in 2003.
I have two problems,
1. the following formula for returning a blank cell when N/A only works in 2003 and not in 2007.

=IF(OR(I3="",J3=""),"",OFFSET('Points Data'!$A$2,MATCH(Drivers!J3,'Points Data'!$B$2:$AM$2),MATCH(Drivers!I3,'Points Data'!$A$3:$A$37))

thanks all

James

appreciate any speedy repsonses!!!

It works OK for me in both ... in that it returns a blank. I have no test data and no plans to produce any so I can't check it any further. It was missing a closing bracket which Excel picked up in both versions.

In what way doesn't it work in 2007 when it works in 2003?

Post a sample workbook in both versions of Excel to demonstrate the problem

Regards

so in 2007 it still spits out NA but it works and spits out a blank in2003.
sorry you're right formula was missing closing bracket
=IF(OR(I3="",J3=""),"",OFFSET('Points Data'!$A$2,MATCH(Drivers!J3,'Points Data'!$B$2:$AM$2),MATCH(Drivers!I3,'Points Data'!$A$3:$A$37)))
attached is the 2007 version it doesnt work in

Look closely at the formulae ... they're not the same.

Clue: check the row number.

Regards

You have this "=IF(OR(I3="",J3=""),..." on Row 4.

Look at Row 3

Regards

yep
so if you delete row 4, forget it ever existed and delete cell J3 on row 3, can you get the formula in row K3 to remain blank rather than N/A?

J3 does not contain null/nothing; it contains a space. Hence the OR condition is FALSE and the two MATCHes are evaluated. The first one fails with #N/A so the overall result is #N/A.

You could use the following formula to cater for space(s) in cells I3 or J3.

=IF(OR(TRIM(I3)="",TRIM(J3)=""),"",OFFSET('Points Data'!$A$2,MATCH(Drivers!J3,'Points Data'!$B$2:$AM$2),MATCH(Drivers!I3,'Points Data'!$A$3:$A$37)))

You don't actually need the MATCHes. The following formula gives the same result:

=IF(OR(TRIM(I3)="",TRIM(J3)=""),"",OFFSET('Points Data'!$A$2,J3,I3))

Note the TRIM function is used to remove space(s) in both formulae.

I'm guessing the second will be quicker to evaluate.

Regards

Forgot - the evidence:

HTML Code: 

Regards

You're welcome. Thanks for the feedback.