Excel比较两列，复杂匹配 [英] Excel comparing two columns, complicated matching

问题描述

我有3列：A，B，C.A和B是字符串，C是整数。我想比较列A和B.如果A1的条目被发现在B的任何位置，那么我希望检查发现B的行中的A的内容是相同的。如果该行的A与B1（我们开始搜索的行的B）相同，则检查C1，找到B。返回C的较低值。

例如：

 标题列A列B列C 
行1 AAA DFD 1 
行2 BBB LAX 1 
行3 DFD AAA 3 
行4 444 DDD 5 
  
解决方案
假设您使用的是Excel 2007或更高版本，并且表格位于A1：C5（带标题在行1），在D2中输入此公式：
  = IFERROR（MIN（C2，INDEX（$ C $ 2：$ C $ 5，MATCH（B2&& A2，INDEX（$ A $ 2：$ A $ 5&|​​& $ B $ 2：$ B $ 5 ,,），0））），）
  
根据需要复制
 
 
 
在C列中没有任何空单元格，所以在做出这个解决方案时，我推测这是不可能的。 （在任何情况下，空白和数字的最小是什么？）
 
 
 
此外，您没有提到结果应该在以下情况下
 
I have 3 columns: A, B, C. A and B are strings and C are integers. I wish to compare columns A and B. If an entry of A1 is found to be anywhere in B, then I wish to check the contents of A in the row where B is found to be the same. If A of that row is the same string as the B1 (B of the row where we started our search), then check C1, and C where B is found. Return the lower value of the C's. 

For example:
Header     Column A   Column B   Column C  
Row 1      AAA        DFD        1
Row 2      BBB        LAX        1
Row 3      DFD        AAA        3
Row 4      444        DDD        5
If I compare A1 with B, it will return 1 (min of 1,3 ) since A1 matches with B3 and A3 matches with B1. I'm not sure how to write this function out on excel. Can anyone help?
解决方案
Assuming you're using Excel 2007 or later, and that your table is in A1:C5 (with headers in row 1), enter this formula in D2:
=IFERROR(MIN(C2,INDEX($C$2:$C$5,MATCH(B2&"|"&A2,INDEX($A$2:$A$5&"|"&$B$2:$B$5,,),0))),"")
Copy down as required.

Your example didn't contain any empty cells in column C, so in making this solution I have presumed that this is not possible. (In any case, what would be the "minimum" of a blank and a number?)

Also, you didn't mention what the result should be in cases for rows where your criteria are not satisfied, so I presumed a blank would suffice.

                        
这篇关于Excel比较两列，复杂匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！