比较两列中的数据，然后复制单元格范围 [英] Compare data in two columns and then copy range of cells

问题描述

我有两个excel的电子表格。

在电子表格1中，我有从A1到A200的单元格中的文本。
在电子表格2中，我有从A1到A200的单元格中的文本。

我想将电子表格1列A中的所有单元格与电子表格中的所有单元格进行比较2列A.如果数据之间存在匹配，我希望单元格输出匹配的行和/或将电子表格B2中的单元格范围B2：F2复制到电子表格1。

我有几个公式，包括match（），index（），但似乎找不到完整的方法。

任何帮助？

解决方案

如果要获取数据值从与电子表格1中具有相同索引值的行中，您可以使用VLOOKUP函数来完成。

  VLOOKUP（A1 ，'C：\PathToWorkBook\ [Workbookname.xlsx] SheetName！$ A $ 1：$ F $ 200,2，FALSE）
  

这将返回Workbookname.xlsx中SheetName中的列B中的值，其中该工作表的列A中的值与当前工作表的单元格A1具有相同的值。如果在Workbookname.xlsx中没有找到相应的值，则此函数将返回＃N / A。还应该注意的是，如果你在A列中的键值不是唯一的，这个函数将只返回从FIRST匹配的结果，而不是告诉你有多个查询键存在。

通过修改第三个参数，您可以更改目标电子表格中正在读取值的列（B至F）。

您还可以使用如果您希望使用

  WorksheetFunction.VLookup 
  

执行相同的功能。然后，您可以将查询的结果直接写入单元格值。

I have two spreadsheets in excel.

In spreadsheet 1 I have texts in cells from A1 to A200. In spreadsheet 2 I have texts in cells from A1 to A200.

I want to compare all the cells in spreadsheet 1 column A to all the cells in spreadsheet 2 column A. If there is a match between the data I want the cell to output the row the match is on and/or copy a range of cells in spreadsheet 2 B2:F2 to spreadsheet 1.

I have come upon a few formulas including match(), index(), but can't seem to find the complete way to accomplish this.

Any help?

解决方案

If you want to get data values from rows with the same index value as you have in spreadsheet 1, you can use the VLOOKUP function to accomplish.

VLOOKUP(A1,'C:\PathToWorkBook\[Workbookname.xlsx]SheetName!$A$1:$F$200,2,FALSE)

This would return the value in column B of from SheetName in Workbookname.xlsx where the value in column A of that sheet has the same value as cell A1 of your current worksheet. If no corresponding value is found in Workbookname.xlsx, this function will return a '#N/A'. It should also be noted that if your key value in column A is not unique, this function will return the results from the FIRST match only and not tell you that multiple look up keys exist.

By modifying the 3rd parameter, you can change which column (B through F) in your target spreadsheet you are reading values from.

You can also use the VLOOKUP in VBA code if you desire using

WorksheetFunction.VLookup

to perform the same functionality. You can then write the result of the look up directly to a cell value.

这篇关于比较两列中的数据，然后复制单元格范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！