On Apr 25, 1:39 pm, "phuser" wrote:
I would like to specify the column in the formula, there will be no user
interaction and it is a financial schedule, and I do have another worksheet
that adds up fields but I was able to get that one easily because I only had
to match field A to A using SUMIF, what I am unable to do is write up the
formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B) for Nov
LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc.

Make sense?

"Spiky" wrote in message

...

On Apr 25, 12:22 pm, "phuser" wrote:
I want to return a value based on column A & B, all the formulas I know
or
have found only allow for a value to be returned when the amount is based
in
1 column but as you can see below I have LY, Budget & TY, for each month,
I
need to specify the column to be returned. This is one of the formula's
I've
tried.

=INDEX(COACCT2008!$C$2:$AL$3000,
MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0))

GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR DEC_BUDGET
DEC_CURRENT JAN_1YR
4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44
4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00

Need more info I think. Like...
How do you want to specify the column to retrieve data from? Will you
type it in once? Or does it require some user input or some other
changing reference? Do you want a different formula to look up each
column, like in a financial schedule of some kind? Ever need to add
multiple columns?

I guess....what exactly are you trying to create?

I think this works.
=SUMPRODUCT((A1:A12=4010)*(B1:B12="PA14"),C1:C12)

This chooses your account from column A, your dept from column B, then
returns the item in column C. You can adjust the formula to do one for
each column you want to reference by changing the C1:C12 range. If the
account numbers are text, put quotes around it instead of just the
number (so: "4010"). Also, the ranges must be identical in size. So,
A1:A12 and B2:B12 will not work in the same formula.