VBA Custom function for lookup

Hi All,

I'm really hoping you can help me out here, I'm absolutely stumped . I'm fairly new to VBA but I have managed to find posts/tutorials on most of my queries - thanks to this site!

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)),""))))))

As you can imagine this gets fairly annoying when copying into separate cells, and i want the ACNLookup function formula (in excel) to appear easy for basic users to edit the referencing cells.

If you could help me out because I'm clueless

Thank you soooo much!

P.S: I have included an example of the spreadsheet to emulate the actual sheet with the data.