
收到粉丝提问:怎么用公式通过K列数值,在E列查找到对应值,并将B、C、D、F列按K列顺序填充到H、I、J、L列中?

从图中可以看到,K列数据是数学经过降序排序后得到的结果,需要将两表的数学字段下的数值整理为一致的,这样才能准确查找匹配对应的数值。
因此,可以先对原表格的数据进行自定义排序,这样做:
按下Ctrl+A选中数据,在开始选项卡下找到排序和筛选,点开下拉按钮,选择“自定义排序”,排序依据选择“数学”,次序选择“降序”,确定。


方法一:INDEX+MATCH函数
对数据完成自定义排序后,可以在H2单元格里输入下面的公式:
=INDEX($B$1:$F$10,ROW(A2),MATCH(H$1,$B$1:$F$1,0))
公式释义:
1、INDEX($B$1:$F$10, ...):
这个函数用于返回指定范围内的值。$B$1:$F$10 是表格的范围,其中包含需要查找的数据。
2、ROW(A2):
ROW(A2) 返回单元格 A2 的行号,即 2。这个值用于指定在 INDEX 函数中要返回的行号。
随着公式向下复制,ROW(A2) 会自动调整为 ROW(A3)、ROW(A4) 等,依次返回 3、4 等行号。
3、MATCH(H$1, $B$1:$F$1, 0):
MATCH 函数用于查找 H1 单元格中的值在 $B$1:$F$1 范围内的位置。$B$1:$F$1 是表格的标题行。0 表示查找精确匹配。
这个函数返回 H1 在标题行中的列号,进而助力INDEX函数返回对应列里的值。

方法二:XLOOKUP函数
XLOOKUP 函数是 Excel 365 和 Excel 2021 中引入的一个更强大和灵活的查找函数。它可以在一个范围内查找指定的值,并返回相应的结果。
我们可以在H2单元格里输入下面的公式:
=XLOOKUP($A2&$K2,$A$2:$A$10&$E$2:$E$10,B$2:B$10,"未找到")
公式释义:
1、$A2&$K2:
这是查找值。将 A2 单元格(序号)和 K2 单元格(数学)的内容连接在一起,作为一个组合查找值。例如,A2 是 “1”,K2 是 90,那么查找值就是 “190”。
2、$A$2:$A$10&$E$2:$E$10:
这是查找数组。将 A2:A10 范围(序号列)和 E2:E10 范围(数学列)的内容连接在一起,形成一个新的查找数组。例如, A2 是 “1”,E2 是 90,那么查找数组中的对应值就是 “190”。
3、B$2:B$10:
这是返回数组。指定了当找到匹配项时,返回 B2:B10 范围内的相应值(即班级)。向右拖动公式,返回数组会随着变动,从而得到想要的数据。
4、"未找到"
这是 if_not_found 参数,指定了当找不到匹配项时返回的值。在本例中,如果找不到匹配项,函数将返回 “未找到”。

以上2种方法都可以根据数学成绩返回其他列的值,具体选择哪种方法取决于你的 Excel 版本和具体需求。若还有疑问,欢迎给小兔留言哦~
想了解更多精彩内容,快来关注