Match Multiple Columns

P

phuser

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=COACCT2008!$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
 
S

Spiky

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=COACCT2008!$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
Click to expand...

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

phuser

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

Dave

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.
If the two values exist in each column, and on the same row, then the
formula returns the data on the corresponding row in Column C.

IF(MATCH(Value1,ColumnA,0)=MATCH(Value2,ColumnB,0),INDEX(ColumnC,MATCH(Value1,ColumnA,0),"")

Is this close to what you want?

Regards - Dave.
 
S

Spiky

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?
Click to expand...

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.
 
P

phuser

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((SUMPRODUCT((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B$3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODUCT((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B$3000=B5),COACCT2008!$C$2:$C$3000*-1)))

works like a charm!!
 
S

Spiky

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((SUMPRODUCT((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B$3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODUCT((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2:$B$3000=B5),COACCT2008!$C$2:$C$3000*-1)))

works like a charm!!
Click to expand...

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.
 
P

phuser

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)*(COACCT2008!$A$2:$A$3000=B21)*(COACCT2008!$B$2:$B$3000=C21),COACCT2008!$C$2:$C$3000)

Any other ideas?
 
P

Pete_UK

Can the cells in column A be equal to A21 AND B21 at the same time?
(well, only if A21=B21).

Do you mean equal to A21 OR equal to B21? If so, try this variation:

=SUMPRODUCT(((COACCT2008!$A$2:$A$3000=A21)+(COACCT2008!$A$2:$A
$3000=B21))*(CO­ACCT2008!$B$2:$B$3000=C21),COACCT2008!$C$2:$C$3000)

Here the + is equivalent to OR.

Hope this helps.

Pete
 
P

phuser

I am kind of thinking there should be a sumif in there somewhere because it
needs to add 2 values from 1 column eg. I have this Formula on a different
sheet that does not include the reference to column c.

=SUMIF(COACCT2008!$A$2:$A$3000,A43,COACCT2008!$AH$2:$AH$3000)+SUMIF(COACCT2008!$A$2:$A$3000,B43,COACCT2008!$AH$2:$AH$3000)))

Can the cells in column A be equal to A21 AND B21 at the same time?
(well, only if A21=B21).

Do you mean equal to A21 OR equal to B21? If so, try this variation:

=SUMPRODUCT(((COACCT2008!$A$2:$A$3000=A21)+(COACCT2008!$A$2:$A
$3000=B21))*(CO­ACCT2008!$B$2:$B$3000=C21),COACCT2008!$C$2:$C$3000)

Here the + is equivalent to OR.

Hope this helps.

Pete
 
P

phuser

this returns a #value

on the right track though?

=SUMPRODUCT(COACCT2008!$B$2:$B$3000=C28)*(SUMIF(COACCT2008!$A$2:$A$3000,A28,COACCT2008!C2:C3000)+(COACCT2008!$A$2:$A$3000,B28,COACCT2008!$C$2:$C$3000))

Can the cells in column A be equal to A21 AND B21 at the same time?
(well, only if A21=B21).

Do you mean equal to A21 OR equal to B21? If so, try this variation:

=SUMPRODUCT(((COACCT2008!$A$2:$A$3000=A21)+(COACCT2008!$A$2:$A
$3000=B21))*(CO­ACCT2008!$B$2:$B$3000=C21),COACCT2008!$C$2:$C$3000)

Here the + is equivalent to OR.

Hope this helps.

Pete
 
P

phuser

This is getting freakier by the try, now I get an actual amount maybe it's
the total COGS *sigh*

Amount returned should be 1514.41 but Im getting 17,907,115.63

=SUMPRODUCT(--(COACCT2008!$B$2:$B$3000=C28))*(SUMIF(COACCT2008!$A$2:$A$3000,A28,COACCT2008!C2:C3000)+(SUMIF(COACCT2008!$A$2:$A$3000,B28,COACCT2008!$C$2:$C$3000)))
 
P

phuser

Thanks for all the help everyone, I got it from just playing around with
Pete's advise,

Your all the best and could never do this wonderful thing called excel
 
Top