Question on multiple variables with a range

  • Thread starter Thread starter Bkfld Dan
  • Start date Start date
B

Bkfld Dan

Hey there,

I have no idea if what I want to do is possible or not, but I am trying to
do the following: I have a table that looks something like this, except with
many more companies, 12 periods per company and several accounts:

Company Period Account 1 Account 2
MAINE 0901 555,149.77 (2,949.98)
MAINE 0902 482,441.13 10,473.65
NH 0901 523,466.17 1,423.65
NH 0902 593,683.45 81,052.68

If I want to use another worksheet to tell me what the value in an account
for a given company and for a specific period, is there a way to use "IF"
functions or VLOOKUPS to do it? I want to be able to change the period every
month to find the value for each company in the easiest possible way.

Thanks!
 
I put your data in A1:D4 (and added some dummy data for testing), so I have
A1:D10
I inserted a new column A with this formula in the new A1 cell =B1&C1 (so it
displays MAINE0901)
In G1, I entered a company code such as NH; and in H1 a period such as 0902
The in cell I1, I used the formula =VLOOKUP(G1&H1,A1:D6,4)
This returns the value 593,683.45
Note that the new column A can be hidden

I expect some someone will show you a formula with INDEX
 
Another way would be to use a Filter (Data | AutoFilter) to get drop downs
that let you select all or any one Period
Your top row will need labels
best wishes
 
I expect some someone will show you a formula with INDEX

No, not INDEX, but maybe SUMPRODUCT

For Account 1:
=SUMPRODUCT((A1:A1000="Maine")*(B1:B1000="0901")*C1:C1000)


For Account 2
=SUMPRODUCT((A1:A1000="Maine")*(B1:B1000="0901")*D1:D1000)


HTH,
Bernie
MS Excel MVP
 
Back
Top