如何在Excel中的两列中对齐匹配值，并在其他列中引入关联值 [英] How to align matching values in two columns in Excel, and bring along associated values in other columns

问题描述

我想知道如何将一列中的值与另一列中的匹配值对齐，如

I want to know how to align values in one column with matching values in another column, as shown in How to align duplicates on the same rows in Excel, but also carry over associated values in other columns.

我有四列显示库存物料编号及其关联的值. A列是从第一个月开始的项目编号的列表.

I have four columns which show inventory item numbers and their associated values. Column A is a list of item numbers from the first month.

B列是第一个月中每个商品编号的值的列表.

Column B is the list of values for each item number in the first month.

C列是第二个月开始的项目编号的列表-它更改了第一个月开始的行数和项目编号.

Column C is a list of item numbers from the second month -- which changes in number of rows and item numbers from the first month's list.

D列是第二个月每个项目编号的值的列表，该值可能会更改，也可能不会更改.

Column D is the list of values for each item number in the second month, which may or may not change.

通过在C中插入一列并使用=IF(ISNA(MATCH(A1,D:D,0)),"",INDEX(D:D,MATCH(A1,D:D,0)))，可以对齐A列和C列. 这样会将相似的商品编号对齐到同一行，但不会对齐它们各自的值.

Column A and Column C can be aligned by inserting a column in C, and using =IF(ISNA(MATCH(A1,D:D,0)),"",INDEX(D:D,MATCH(A1,D:D,0))). This will align the like item numbers onto the same row, but will not align their respective values.

如何使此功能也带入B列和E列中的关联值，以便最终显示在同一行上显示第一个月的项目编号及其值与第二个月的同一项目编号及其值(四列)都在同一行上(这样就可以计算出值的差)?

How can this function be made to also bring along the associated values from Columns B and E, so that the final display shows the first month's item number and its value on the same row as the same second month's item number and its value (in four columns) all on the same row (so the difference in value can be calculated)?

示例:

Col A Col B  Col C  Col D
ITEM VALUE   ITEM   VALUE
11    $11     11     $9
12    $15     12     $16
13    $13     15     $21
14    $8      17     $24
15    $12     

推荐答案

跳过所有这些.下载Microsoft FUZZY LOOKUP加载项.使用您的列创建表.创建一个新的工作表.将表输入到工具中.单击所有相应的列复选框.使用滑块进行完全匹配.赶快去等魔术吧.

Skip all of this. Download Microsoft FUZZY LOOKUP add in. Create tables using your columns. Create a new worksheet. INPUT tables into the tool. Click all corresponding columns check boxes. Use slider for exact matches. HIT go and wait for the magic.

这篇关于如何在Excel中的两列中对齐匹配值，并在其他列中引入关联值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！