Excel LOOKUP 匹配错误的单元格

Question

我试图在 Excel 中完成一项相当简单的任务，但没有得到我想要的结果。我有一个简单的时间表，通过填写一张简单的表格，我将一组教练分配给一系列比赛。这是缩小版:

  Match | John | Pete | Chris |       
 -------|------|------|-------|------- 
      1 | X    |      |       | John  
      2 |      | X    |       | Pete  
      3 | A    |      | X     | Chris 
      4 | X    |      | A     | Chris (!)
      5 |      | X    | A     | Pete  

图注:X:将执教； - 答:是可用的。

我使用该表来注册可用性，然后将每行中的一个 A 更改为一个 X，以选择实际指导比赛的人。

为了进行概述，我决定添加一个列，其中将显示所选教练。我使用了以下公式: =LOOKUP("X"; B2:D2; B$1:D$1)对于第 2 行并将其复制到其他行，以便每行的行号与放置公式的行相对应。

令我惊讶的是，第 4 场比赛被分配给了 Chris，而 John 有一个 X，而 Chris 只有一个 A。

当我阅读 Microsoft's documentation on LOOKUP ，我注意到一些事情:

LOOKUP 有向量形式和数组形式。微软建议对数组形式使用 HLOOKUP，但我使用向量形式。我不认为 HLOOKUP 对我有用，因为它只在指定数组的第一行中查找值，而我的第一行包含要返回的值。

上面写着:“一个只包含一行或一列的范围。”，还有“重要提示:lookup_vector 中的值必须按升序排列:..., -2, -1, 0, 1, 2, . .., A-Z, FALSE, TRUE；否则，LOOKUP 可能不会返回正确的值。大写和小写文本是等效的。”

我认为 2. 是导致问题的原因。我不确定如何对 A2:A4 之类的范围参数进行排序。 Microsoft 记录了 SORT function ，但它是测试版。另外，无论如何，我认为对搜索行进行排序会弄乱匹配。

我发现的解决方法是将我的代码更改为 A:已分配和 B:备份，其中 A 和 B 被选择为按字母顺序升序。如果我更改公式以使用查找值“A”，这给了我:

  Match | John | Pete | Chris |       
 -------|------|------|-------|------- 
      1 | A    |      |       | John  
      2 |      | A    |       | Pete  
      3 | B    |      | A     | Chris 
      4 | A    |      | B     | John  
      5 |      | A    | B     | Pete  

这就是我想要的结果。

任何人都可以阐明这种完全违反直觉的行为和/或描述实现这一目标的替代方法吗？

笔记:

我只在每行放 1 个 X。

我在 Microsoft Excel for Office 365 MSO (16.0.11328.20362) 64 位和 Microsoft Excel 2016 MSO (16.0.12130.20232) 32 位中看到了这一点。

Answer 1

根据文档，LOOKUP 完全按照它的设计目的进行。您应该使用 INDEX和 MATCH :

=INDEX($B$1:$D$1;MATCH("X";B2:D2;0))

MATCH 的最后一个 0 参数意味着您正在寻找完全匹配的数据，因此不需要对数据进行排序。