excel - 如何在excel "vlookup"中搜索多个列并从同一行的另一列返回值？

Question

我有一个excel文件。
它的目的是列出房间(工作室)和人名，所以我可以知道两件事:

谁嵌入每个房间。

什么是特定人的房间。

该文件中有 2 个电子表格。
第一个(称为“人”)是一个名称列表，另一列说明每个人的房间是什么。注意:没有两个相同的名称。每个名字都不一样。

第二个(称为“房间”)是 5 列的列表:

第一列是房间号

另外4个，是4个人的名字(因为每个房间里有4个人)。注意:房间里的人的顺序(在 4 列中)并不重要。

当我想从不同房间交换两个人时，问题就开始了。然后，我必须在两个电子表格中更改它。
我想找到一个公式，在“房间”电子表格中找到一个名称，并将房间号(A 列)写在 B 列“人员”电子表格的正确行中。
我尝试使用 lookup 之一公式(尤其是 vlookup )，但问题是这些函数仅在第一列中搜索并从其他列返回值，而我需要完全相反:我想从其中一个中查找值(特定名称) 4 列，并返回第一列的值(房间号 - A 列)。这样我可以转到“人员”电子表格中的 B 列，并为其中的每个名称插入一个公式，这样它就可以找到正确的房间号。
另一个问题是“房间”电子表格中的房间号不一定是排序的。有时我想带一个小组，把它搬到另一个房间。在这种情况下，我所做的只是更改 A 列中的房间号，这会使列表未排序。
如何使用公式来做到这一点，我只需要更改“房间”电子表格中的人员，而不需要更改“人员”电子表格中的房间号？是否有一个公式可以满足我的需要，或者我应该使用多个公式来实现我的目标？

Answer 1

使用 INDEX & AGGREGATE 功能
• 单元格中使用的公式B4

=INDEX(rooms!$A$4:$A$8,AGGREGATE(15,6,(ROW(rooms!$B$4:$E$8)-ROW(rooms!$B$4)+1)/(people!$A4=rooms!$B$4:$E$8),1))

那么让我解释一下 AGGREGATE()函数返回每个emp的位置，实际上是返回emp所在的行号

AGGREGATE(15,6,(ROW(rooms!$B$4:$E$8)-ROW(rooms!$B$4)+1)/(people!$A4=rooms!$B$4:$E$8),1)

接下来当包裹在 INDEX() 中时它的功能是根据需要输出房间号。
修改后引用下图!