Problem with dynamic ranges

[Guest]

I generate a worksheet from my accounting software and do a copy and paste to
insert it into an existing worksheet that contains named ranges. The
spreadsheet also contains period to date and year to date worksheets that are
updated according to main account and sub-account numbers on the sheet I
import. My problem is that new accounts are added every month so my named
ranges keep changing. Right now I'm updating it manually but there are over
3000 lines in the worksheet where the ranges change by 1 or 2 cells.
My question is, does anyone know how I can do a lookup within my existing
formula that would look up the account number in column a and sub-account
number in column b and return the value of column 9 in that row? This is the
current formula where $A149 is the main account number and AUB is my current
named range.
=IF(ISNA(VLOOKUP($A149,AUB,9,FALSE)=7),0,VLOOKUP($A149,AUB,9,FALSE))
I'm using Excel 2002

Any help would be appreciated!

Thanks
Jayne

 

[Guest]

You can use the INDEX function as shown in the formula below to return
columns 1, 2, and 9 of your named range, then use the versatile SUMPRODUCT
function on those columns to yield your desired lookup value. I've assumed
$A150 contains the subaccount number, so adjust as necessary.

=SUMPRODUCT((INDEX(AUB,,1)=$A149)*(INDEX(AUB,,2)=$A150)*INDEX(AUB,,9))

This will return 0 if the acct and subacct pair is not found. This also
assumes any acct and subacct pair appears at most once.