需要在Excel中交叉引用两个供应商名称/编号列表 [英] Need to cross reference two lists of Vendor Names/Numbers in Excel
问题描述
我有两个供应商名称/数字列表,共四列。我附上了示例数据的图片
I have two lists of vendor names/numbers in four total columns. I've attached a picture of sample data
..
我需要做的是,如果列D中的供应商名称在列B中,我需要提取两个供应商编号,以便我可以比较两个并创建交叉引用列表。
What I need to do is if a vendor name in column D is in column B, I need to pull both vendor numbers so that I can compare the two and create a cross reference list.
所以,在我的例子中,我需要拉A2 / B2来与C3 / D3比较。
So, in my examples, I need to pull A2/B2 to compare with C3/D3.
我该怎么办?
推荐答案
要获取匹配列表,请使用此Array公式:
To get the list of matches, use this Array formula:
=IFERROR(INDEX($B$2:$B$5,SMALL(IF(COUNTIF($D$2:$D$5,$B$2:$B$5),ROW($1:$4)),ROW(1:1))),"")
我将其放在F2中,并向下复制。
I put it in F2 and Copied down.
然后引用该列我使用以下两个公式:
Then referencing that column I used the following two formulas:
=IF(F2<>"",INDEX(A:A,MATCH(F2,B:B,0)),"")
=IF(F2<>"",INDEX(C:C,MATCH(F2,D:D,0)),"")
编辑
对于大量数据,并使用现有列D作为后两个公式的参考:
For large amounts of data forget the first formula and use the Existing column D as the reference for the two latter formulas:
=IFERROR(INDEX(A:A,MATCH(D2,B:B,0)),"")
=IFERROR(INDEX(C:C,MATCH(D2,D:D,0)),"")
然后按降序排序按栏目E:
Then Sort descending by column E:
这篇关于需要在Excel中交叉引用两个供应商名称/编号列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!