excel - 多列与多列查找

Question

我正在寻找一个公式来匹配两个工作表之间的许多列并返回最后一个引用工作表的最终列数据。我知道这在 VBA 中是可行的，但我正在寻找一种公式方法。

主工作表:

User  | Region   | Country | City | Lookup
--------------------------------------------------
User1 | Europe   | Italy   | Rome | [formula here]
User2 | Americas | Brazil  | Rio  | [formula here]

引用工作表:

Region   | Country | City   | Data
-----------------------------------
Europe   | England | London | some data
Americas | Brazil  | Rio    | more data
Europe   | Italy   | Rome   | some more data

我所使用的公式应该匹配该特定行中的每一列，并将 ReferenceWorksheet 中的数据单元格值添加到 MainWorksheet。

eg. If (MainWorksheet.Region = ReferenceWorksheet.Region) &&
       (MainWorksheet.Country == ReferenceWorksheet.Country) && 
       (MainWorksheet.Region == ReferenceWorksheet.Region) Then
    MainWorksheet.Column E = ReferenceWorksheet.Current Row:Data Column

我还没有找到一种干净的方法来使用 VLOOKUP、INDEX(MATCH)) 等使用多个列来执行此操作。有没有办法在函数内进行过滤？

非常感谢任何帮助!

Answer 1

我同意 vasek1 的观点，添加额外的列将简化所需的公式，但如果您想避免额外的列，可以使用[相对]简单的方法。

方法 1 - 执行与 vasek1 相同的串联...但在公式内，例如在E2主要

=INDEX(Ref!D$2:D$100,MATCH(B2&"-"&C2&"-"&D2,Ref!A$2:A$100&"-"&Ref!B$2:B$100&"-"&Ref!C$2:C$100,0))

需要使用 CTRL+SHIFT+ENTER 确认公式

方法 2 - 使用 LOOKUP 的非数组版本

=LOOKUP(2,1/(Ref!A$2:A$100=B2)/(Ref!B$2:B$100=C2)/(Ref!C$2:C$100=D2),Ref!D$2:D$100)

请注意，第一个公式查找第一个匹配项，后者查找最后一个匹配项。我假设引用数据只有每个地区/国家/城市组合的一个实例，在这种情况下它们都会给出相同的结果，但并不能保证在每种情况下都如此。

允许 C2 为 "<>"意思是“任何国家/地区”(根据评论)，您可以使用此修订版的 LOOKUP 公式

=LOOKUP(2,1/(Ref!A$2:A$100=B2)/((Ref!B$2:B$100=C2)+(C2="<>"))/(Ref!C$2:C$100=D2),Ref!D$2:D$100)

类似的更改可以应用于 INDEX/MATCH 版本