2

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.

bmath
  • 23
  • 1
  • 6
  • So do you need to be in the same row in column C and column D for it to return a match or only see that A2 matches somewhere in column C and B2 matches somewhere in column D? – gtwebb Jun 15 '15 at 20:09
  • Yes, you must be in the same row in Column C and D. – bmath Jun 15 '15 at 20:16

3 Answers3

2

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

gtwebb
  • 2,981
  • 3
  • 13
  • 22
  • The first formula is the one I am looking for, do you mind explaining the formula further? I don't understand how the Sumproduct works. Also could I use something like ($C:$C=A1)? I also don't understand what the -- are doing, and the >0,1,0. Is that what is telling it what to return when it finds the statement true? The reason I wanted to return a 1 in E column was so that I could use conditional formatting to highlight every row where A & B find corresponding matches in C & D. – bmath Jun 15 '15 at 20:28
  • Thank you so much for all your help thusfar, I really appreciate it. – bmath Jun 15 '15 at 20:28
1

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

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
0

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

crishoj
  • 5,660
  • 4
  • 32
  • 31