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.
+-------------------------------------------------------------------+
|Filename: ACNLOOKUP().zip |
|Download:
http://www.excelforum.com/attachment.php?postid=3796 |
+-------------------------------------------------------------------+
--
Sami82
------------------------------------------------------------------------
Sami82's Profile:
http://www.excelforum.com/member.php...o&userid=27111
View this thread:
http://www.excelforum.com/showthread...hreadid=466252