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

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 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?

Hi,
Not sure if I have understood your need properly, but here's a suggestion.
The following formula looks for 'Value1' in Column A, and 'Value2' in Column
B.

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.

Thank you so Much Spikey, was able to do exactly what I was looking for and
more :-)

my version of your formula

=IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000*-1)))

works like a charm!!


"Spiky" wrote in message
...
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.

On Apr 30, 11:50 am, "phuser" wrote:
Thank you so Much Spikey, was able to do exactly what I was looking for and
more :-)

my version of your formula

=IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000*-1)))

works like a charm!!

"Spiky" wrote in message

...

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.

No problem.

Just watch out if you have a lot of these. SUMPRODUCT is an array
function by default, so it can slow everything down with too much
calculation.

Just when you think you got it all figured out, you need something else
*sigh*

Now Im trying to filter by 2 criteria in COACCT2008 ColumnA, this returns a
0.00 and I'm starting to think that the SUMPRODUCT does not work if you are
using the same column.

=SUMPRODUCT((COACCT2008!$A$2:$A$3000=A21)*(COACCT2 008!$A$2:$A$3000=B21)*(COACCT2008!$B$2:$B$3000=C21 ),COACCT2008!$C$2:$C$3000)

Any other ideas?



"phuser" wrote in message
news:t5adnaixDqvAPoXVnZ2dnUVZ_jGdnZ2d@sasktel...
Thank you so Much Spikey, was able to do exactly what I was looking for
and more :-)

my version of your formula

=IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODUC T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B $3000=B5),COACCT2008!$C$2:$C$3000*-1)))

works like a charm!!


"Spiky" wrote in message
...
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.