If statements & vlookup

[Lee75_UK]

Hi,

I am using excel 2003 and trying to make a lookup work based on 3 different scenarios.

In sheet 1 (named data) I have the following columns.

A = ABV (can be a range between 2.0 & 9.0)
B = Date (production date)
c = Rate (Tax rate, based on the tax rate on a specific date)

In sheet 2 (named lookup) i have the following columns.

A = Date (production date)
B = Tax rate 1 between 2.9 & 7.5 = £18.57
C = Tax rate 2 <= 2.8 = £9.29
D = Tax rate 3 > 7.5 = £23.21

What I am trying to do is if 'Data A' = > 7.5 then use the production date in 'Data B' and match that to the date in 'lookup A' and return the rate from 'lookup D'

and the same for the other 2 tax rate ranges.

I have created the following formula but in when I enter a tax rate of 2 or 3, i get #REF returned.

=IF(A2>7.5,VLOOKUP(B2,lookup!A3:D32,4,0),IF(A2<=2.8,VLOOKUP(B2,lookup!A3:D32,3,0),VLOOKUP(B2,lookup!A3:D32,2,0)))

Note, this formula works as expected in excel 2007.

Any help is much appreciated.

 

[Magriza]

I don't see why the formula wouldn't work in 2003...

I would suggest replacing the column number with an IF statement as that's the only part that changes. Or use something like:
=VLOOKUP(B2,lookup!A3:D32,CHOOSE(MATCH(A2,{0,2.9,7.5,7.6},1),3,2,2,4),FALSE)