Excel：map、映射——根据一列的取值自动填充另一列对应的值

背景

sheet1中有两列，代表了一个映射关系。

 sheet2中也有两列，第一列和上表第一列类似（但是顺序打乱了，而且可能存在多值、少值的情况）

 如何构建一个映射，将sheet1中的第二列自动根据sheet2第一列的取值填充到第二列？最终期望得到一个这样的表

公式

index(Value列，MATCH(KEY值，KEY列，0))

Value列：查找的K-V表的Value列，即sheet1的2列

KEY值：要填充的Value对应的哪个Key，例如“王五”就是A2

KEY列：查找的K-V表的KEY列，即sheet1的1列

最终的公式为：

 之后用向下拉动的方式自动填充即可：

 

补充：

1、INDEX函数

写法：INDEX(数组，行数，列数)

用途：根据坐标，从一组单元格中找到该坐标处的数值

注意：

　　1）如果数组为二维数组，那么行数、列数至少为1。

　　例如

　　要获取李四的数学成绩，写法就是：

　　=INDEX(B2:D4,2,2)

 　　即这片区域第二行第二列的数值。

　　2）如果数组为一维数组，那么行数、列数中必须有一个为0：

　　　　如果是一列，那么列数必须为0，且行数必不为0。

　　　　如果是一行，那么行数必须为0，且列数必不为0。

　　　　补充：

　　　　　　①如果上述不为0的行、列写成了0，最后会显示#SPILL!

　　　　　　②实际用的时候，如果是一维数组，那么后边参数为0的行、列可以不写，EXCEL会自动识别是列数还是行数。

　　例如：

　　　　①获取李四的语文成绩：80

　　　　此时选中语文列，行号设为2，列号为0

　　　　②获取王五的英语成绩：97

　　　　此时选中王五行，行号设为0，列号设为3

　　　　

 

因此用好INDEX的关键在于后两个参数，即坐标的构建。

 

例子

　　1、如果想把某一列每一行的值各重复3次。可以这样写：

　　=INDEX($B$2:$B$4,(ROW(B2)+1)/3)

　　即把2、3、4行映射到1，所以上文要(行号+1)/3

　　结果：

　　之后的其他情况都可以从中延伸。

 

2、MATCH函数

写法：

　　①MATCH（值，值所在的列，0）

　　②MATCH（值1&值2，值1所在的列&值2所在的列，0）

　　如果跨表，则最后确认时需要用CTRL+SHIFT+ENTER进行计算。

用途：

　　获取某个（些）值所在的行号。

例子：

　　从一堆字母对中，找到A、C所在的行号：