Excel:将两列与其他两列匹配 [英] Excel: match two columns with two other columns

查看:54
本文介绍了Excel:将两列与其他两列匹配的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在excel中,我有四列.列A&B彼此对应,并且列C& A与B对应.D彼此对应.我想做的是创建一个公式,该公式从A列获取一个值,在C列中搜索,寻找匹配项.如果找到匹配项,它将从B列中获取相应的值(同一行),并在D列中搜索与B列中的值匹配的值.如果两个值最终都找到匹配项,则返回1E列中的A&B进来了.

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.

这是我到目前为止在E列中输入的信息.

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

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

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

我的问题是,我不知道如何为B进行匹配,仅检查A找到匹配项的行.

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.

谢谢.

推荐答案

我将给出两个答案.

第一个要求匹配项在列C和D中位于同一行.因此,如果A2匹配C3,C4,C5,那么B2将需要匹配D3,D4或D5

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)

由内而外-($ C $ 1:$ C $ 5 = A1)将A1与C1到C5中的所有值进行比较,如果为true,则返回1,如果为false,则返回0Sumproduct将这些乘积相乘,因此您需要两面都为真如果仅使用if语句将数字限制为1(如果2行匹配等,它将返回2)

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)

根据问题进行扩展-是的,您可以在公式中使用-(C:C = A1),因为它可以处理更多的单元格,因此计算时间会更长

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)将返回一个类似于 {0,0,0,1,0,0,1} 的数组,其中每次C列中的一个单元等于A1时为1.
-(D:D = B1)公式每次在D中的一个单元格与B1匹配时都用1s表示相同. {0,1,0,0,0,0,1} .Sumproduct将这些数组乘以 {0 * 0,1 * 0,0 * 0,1 * 0,0 * 0,0 * 0,1 * 1} 并加结果(0+ 0 + 0 + 0 + 0 + 0 + 1)= 1 .

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

此sumproduct公式可以返回任何整数值(如果没有匹配项,则返回0;如果有一组匹配的单元格,则返回1;如果有两组匹配的单元格,则返回2,等等).

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

通过将其包装在if(sumproduct(..)> 0,1,0)中只是意味着它将始终返回0(不匹配)或1(一个或多个匹配项).

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

希望这会有所帮助.

不需要公式

下一个公式看起来要与C列中的A2匹配,然后看起来要与D列中的B2匹配,而不必是同一行.

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)

您知道的比赛如果没有匹配项,iferror只会返回0如果值大于0(如果两列都匹配),则if语句仅返回1.

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

这篇关于Excel:将两列与其他两列匹配的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆