我目前有两列需要比较。Col A,工作表 1 & Col A,工作表 2。
第 1 页包含:
A B C
5000 Apples WI
6182 Oranges NY
7271 Grapes MN
2293 Peanuts FL
第 2 页包含:
A
4032
5233
7271
2293
应收到如下结果....
7271 Grapes MN
2293 Peanuts FL
我只需要显示包含与 Sheet 2 相同数字的结果。有没有比将 Sheet 2 加载到数组中并将其与 Sheet 1 中的每个单元格进行比较更好的方法?
'For i = 1 to Sheet1LastRow
Sheet2Row = Range("A" & Rows.Count).End(xlUp).Row
Sheet2Array = Range("A2:A" & Sheet2LastRow).Value
For i = LBound(Sheet2Array, 1) To UBound(Sheet2Array, 1)
'if cell = Sheet2Array(i, 1)
'....
'End if
Next i
'Next Cell sheet 1
答案1
这是 的核心功能=VLOOKUP()
。
语法是:
=VLOOKUP(
compare this cell,
to the cells in the leftmost column of this range,
returning the corresponding value from this column index,
true/false for range lookup (just leave this false if you aren't sure)
)
在工作表 2 的 B1 单元=VLOOKUP(A1,Sheet1!A:B,2,False)
格中放置=VLOOKUP(A1,Sheet1!A:C,3,False)
此函数在工作表之间和工作簿之间均可使用(但您必须启用链接并注意两个工作簿的更改)。无需 VBA,这是一个简单的内联函数。
在此基础上,您可能会收到#N/A
源表中不存在的值的错误。将公式包装进去,=IFERROR( your vlookup() function , "" )
用空白单元格替换所有错误。
答案2
从您的问题和样本数据可以明显看出,您正在寻找的输出是连续单元格块中包含匹配数据的列,中间没有任何空白或错误。
如果您希望使用公式方法,那么可以使用数组公式中稍微复杂的 INDEX 和 MATCH 函数来实现。一旦您在列中的连续单元格中拥有匹配的数据,只需应用 VLOOKUP 从 Sheet1 中的主表中获取其余两列。
方法如下。下面是 Sheet1 和 Sheet2 的两张屏幕截图。
Sheet1 在 A、B 和 C 列中包含您的主表,Sheet2 中包含与 Sheet1 中的 A 列匹配的列表
现在在 Sheet2 中的单元格 C1 中输入以下数组公式
{=INDEX(Sheet1!$A$1:$A$4,SMALL(IF(ISERROR(MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),"",MATCH(Sheet2!$A$1:$A$4,Sheet1!$A$1:$A$4,0)),ROW(1:1)))}
将此公式放在括号外,然后在公式栏中按 CTRL+SHIFT+ENTER 创建数组公式,并将其向下拖动,直到单元格或要匹配的列表长度出现错误。现在,错误单元格上方的所有行都有来自 Sheet1 的 A 列的匹配单元格。
只需清除错误单元格,您就会获得连续单元格块中的列表。
现在在 D1 中输入相对简单的 VLOOKUP 公式来从 Sheet1 中获取下一个匹配的列
=VLOOKUP(C1,Sheet1!$A$1:$C$4,2,FALSE)
在 E1 中输入公式
=VLOOKUP(C1,Sheet1!$A$1:$C$4,3,FALSE)
并将两者向下拖动。您便获得了所需的列表。
您可以使用 Excel 内置的“评估公式”选项来评估 INDEX MATCH 组合,以了解其工作原理。它会创建一个匹配行号的数组,并在您向下拖动公式时选择第一个、第二个、第三个最小的数字。
这里有两个限制:数据应按照它在主表中存在的顺序返回,而不是按照它在“要匹配”表中的顺序返回,并且如果有多个匹配的单元格,则应返回第一个。