CODEWITHSUNDEEP
excel
bmath
bmath

Reputation: 33

Excel: match two columns with two other columns

in excel, I have four columns. Columns A & B correspond with each other and columns C & D correspond with each other. What i'd like to do is create a formula that takes a value from column A, searches through column C, looking for a match. If it finds a match, it will then take the corresponding value (same row) from column B, and search for a value in column D that matches with the value from column B. If both values end up finding matches, return a 1 in column E, in the row that A & B are in.

This is what I have so far, entered in column E.

=IF(MATCH(A1,$C:$C,0)MATCH(B1,$D:$D,0),1,0)

My issue is that I don't know how to make the match for B check only the row where A found a match.

If there are any issues understanding my question or if you need me to help clarify something, please let me know. I've had this problem for a few days now and can't seem to figure it out. I'll be actively checking this thread for the next hour.

Thank you.

Upvotes: 3

Views: 11237

Answers (3)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15641

This will do the trick

=SUMPRODUCT((C:C=A1)*(D:D=B1))

You can narrow the spanned height (e.g., =SUMPRODUCT((C1:C100=A1)*(D1:D100=B1)))

Upvotes: 1

gtwebb
gtwebb

Reputation: 3011

I'll give two answers.

The first requires the matches to be in the same rows for column C and D. So if A2 matches C3,C4,C5 then B2 will need to match D3,D4 or D5

=IF(SUMPRODUCT(--($C$1:$C$5=A1),--($D$1:$D$5=B1))>0,1,0)

From the inside out --($C$1:$C$5=A1) compares A1 to all the values in C1 to C5 and returns a 1 if true and a 0 if false Sumproduct multiplies these together so you need to get trues in both sides If statement is just used to limit the number to 1 (it would return 2 if 2 rows matched etc)

To expand based on questions -Yes you could use --(C:C=A1) inside the formula it just takes much longer to compute since it is working on many more cells

--(C:C=A1) will return an array that looks like this {0,0,0,1,0,0,1} with a 1 everytime a cell in column C is equal to A1.
--(D:D=B1) formula will do the same with 1s everytime a cell in D matches B1 say {0,1,0,0,0,0,1}. Sumproduct multiplies those arrays {0*0,1*0,0*0,1*0,0*0,0*0,1*1} and adds up the results (0+0+0+0+0+0+1) = 1.

This sumproduct formula can return any interger value (0 if there is no match, 1 if there is one set of matched cells, 2 if there are 2 sets of matched cells, etc).

By wrapping it in an if(sumproduct(..)>0,1,0) just means it will always return a 0 (no match) or 1 (one or more matches).

Hope this helps.

Edit: Formula not needed

Next formula looks to match A2 somewhere in column C, and the then looks to match B2 somewhere in column D doesn't have to be the same row.

=IF(IFERROR(MATCH(A1,$C$1:$C$5,0)*MATCH(B1,$D$1:$D$5,0),0)>0,1,0)

match you know iferror just makes it return 0 if nothing matches If statement just returns a 1 if the value is anything >0 (if both columns have a match).

Upvotes: 3

crishoj
crishoj

Reputation: 5927

Use ISERROR to check if MATCH finds the value you are looking for or not.

Upvotes: 0

Related Questions