Sami82 wrote...
....
I am trying to create a custom function in VB to replace a 7 level
lookup. I want the function to go something like this:

=ACNLookup(State,product,date)

I currently have the following excel formula in place (beware its
extremely messy).

=IF($C7="NSW",OFFSET('ACN-NSW'!$A$3,
MATCH(Claim!$B7,'ACN-NSW'!$A$4:$A$30,0),
MATCH(Claim!D$6,'ACN-NSW'!$B$3:$IV$3,0)),
IF($C7="QLD",OFFSET('ACN-QLD'!$A$3,
MATCH(Claim!$B7,'ACN-QLD'!$A$4:$A$30,0),
MATCH(Claim!D$6,'ACN-QLD'!$B$3:$IV$3,0)),
IF($C7="VIC",OFFSET('ACN-VIC'!$A$3,
MATCH(Claim!$B7,'ACN-VIC'!$A$4:$A$30,0),
MATCH(Claim!D$6,'ACN-VIC'!$B$3:$IV$3,0)),
IF($C7="SA",OFFSET('ACN-SA'!$A$3,
MATCH(Claim!$B7,'ACN-SA'!$A$4:$A$30,0),
MATCH(Claim!D$6,'ACN-SA'!$B$3:$IV$3,0)),
IF($C7="TAS",OFFSET('ACN-TAS'!$A$3,
MATCH(Claim!$B7,'ACN-TAS'!$A$4:$A$30,0),
MATCH(Claim!D$6,'ACN-TAS'!$B$3:$IV$3,0)),
IF($C7="WA",OFFSET('ACN-WA'!$A$3,
MATCH(Claim!$B7,'ACN-WA'!$A$4:$A$30,0),
MATCH(Claim!D$6,'ACN-WA'!$B$3:$IV$3,0)),
""))))))
....

Looks like you could replace this with

=IF(OR($C7={"NSW","QLD","VIC","SA","TAS","WA"}),
OFFSET(INDIRECT("'ACN-"&$C7&"'!$A$3"),
MATCH(Claim!$B7,INDIRECT("'ACN-"&$C7&"'!$A$4:$A$30"),0),
MATCH(Claim!D$6,INDIRECT("'ACN-"&$C7&"'!$B$3:$IV$3"),0)),"")