多列与多列查找

问题描述

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

主要工作表：

 用户|区域|国家|城市|查找
 --------------------------------------------- ----- 
 User1 |欧洲|意大利|罗马| [formula here] 
 User2 |美洲|巴西|里约| [公式] 
  

参考表：

  Region |国家|城市|数据
 ----------------------------------- 
欧洲|英格兰|伦敦|一些数据
美洲|巴西|里约|更多数据
欧洲|意大利|罗马|一些更多的数据
  

我以后的公式应该匹配该行中的每个列，并添加数据单元格值从ReferenceWorksheet到MainWorksheet。

  eg。如果（MainWorksheet.Region = ReferenceWorksheet.Region）&& 
（MainWorksheet.Country == ReferenceWorksheet.Country）&& 
（MainWorksheet.Region == ReferenceWorksheet.Region）然后
 MainWorksheet.Column E = ReferenceWorksheet.Current行：数据列
  

我没有找到一个清除方式来使用多列使用VLOOKUP，INDEX（MATCH））等。有没有办法过滤功能？

任何帮助非常感谢！

解决方案

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

方法1 - 执行与vasek1 ....相同的连接公式，例如在E2
主要

= INDEX（Ref！D $ 2：D $ 100，MATCH（B2&& - &安培; D2，参考文献A $ 2：！甲$ 100安培; - &安培;参考文献B $ 2：B 100 $&安培; - &安培;参考文献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版本

I am after a formula to match a number of columns between two worksheets and return the last reference worksheets final column data. I know this is doable in VBA, but am looking for a formula method.

MainWorksheet:

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

ReferenceWorksheet:

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

The formula I am after should match each column in that particular row and add the Data cell value from the ReferenceWorksheet to the MainWorksheet.

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

I haven't found a cleancut way to do this using mutliple columns using VLOOKUP, INDEX(MATCH)) etc. Is there a way to filter within a function?

Any help is much appreciated!

解决方案

I agree with vasek1, adding additional columns will simplify the formulas required but if you want to avoid extra columns there are [relatively] simple methods available.

Method 1 - do the same concatenation as vasek1....but within the formula, e.g. in E2Main

=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))

formula needs to be confirmed with CTRL+SHIFT+ENTER

Method 2 - a non-array version with 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)

Note that the first formula finds the first match, the latter the last. I assume that the reference data will only have a single instance of each region/country/city combination in which case they will both give the same results, but that isn't guaranteed in every situation.

To allow C2 to be "<>" meaning "any country" (as per comment) you can use this revised version of the LOOKUP formula

=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)

A similar change can be applied to the INDEX/MATCH version

这篇关于多列与多列查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！