多标准索引匹配 [英] Multiple criteria index matching

问题描述

我已经花了几个小时尝试这样做，在线发现文章，但似乎没有工作。

我有一个Excel文档，其中包含两个选项卡上的数据并且想要下面的公式告诉我，如果在两种形式上的日期和用户名匹配，称为提升来源& Sheet1

  {= INDEX（Sheet1！F：F，MATCH（'Raised Source'！E：E&'Raised来源'！F：F，Sheet1！A：A& Sheet1！B：B，0））} 
  

这会抛出一个＃N / A错误

帮助!!!

解决方案

首先我假设您的数据采用以下格式：

而您正在使用Raised Source！通过 Sheet1！搜索，并从 Sheet1！返回团队。

公式：

公式需要作为数组输入（一旦复制并粘贴，而仍在公式栏中，请点击 CTRL + SHIFT + ENTER ）

= IFERROR（INDEX（Team，MATCH（1，（E1 = TeamDate）*（F1 = UserID），0）），）

我已经使用命名范围来使这些公式更为简单：

 团队= Sheet1！$ F $ 1：$ F $ 6 
 TeamDate = Sheet1！$ A $ 1：$ A $ 6 
 UserID = Sheet1！$ B $ 1：$ B $ 6 
  

请注意，在整个列使用数组公式时，计算速度显着下降，因此最好避免并定义正在搜索的确切范围逻辑：

$ b

= INDEX（array，row，[column]） - 此公式将'index'您指定的数组，可以告诉它您要返回的行号和列号（例如 = INDEX（B3：C4,1,1）将返回第一行中的值， B3：C4 的第一列 B3 。如果我们只想要返回第一列，或者我们正在寻找结果的数据只有1列宽，我们不需要告诉公式一列。

这是公式来的地方。我们使用 MATCH（lookup_Value，Lookup_array，[match_type]）公式告诉我们什么位置（在这种情况下是什么行）的价值我们正在寻找发生。

正常使用 MATCH（）将匹配范围内的确切单元格内容，但因为我们使用数组公式，我使用 MATCH（1，（E1 = TeamDate）*（F1 = UserID），0）

所以，我已经告诉公式找到 1 并告诉我发生这种情况的立场。当两个条件与Excel公式匹配时， 1 返回， TRUE 表示 1 和 FALSE 表示 0 。当这个公式通过一行一行工作时，如果两个结果都匹配并返回 TRUE ，则数组将存储 1 * 1 这是 1 ，当然如果两者都是 FALSE ，那么它将返回 1 * 0 ， 0 * 1 或 0 * 0 所有这些都导致 0 。

由于命名范围全部从同一行开始，相对位置（或多少次，直到 1 出现）始终是您希望 INDEX（）公式返回的行号。

IFERROR（value，value_if_error） - 如果没有找到匹配项，该公式会抛出一个错误，因为它不能匹配 1 到我已经构建的数组。如果发生这种情况，我已经告诉我的公式，我希望它返回，）而不是一个空白单元格。您可以将其更新为您想要的任何文字，也许，Not Found）？

另一个答案中的另一个IF（）是特定于该问题的，因为他们只想知道有一个匹配，而不是从该行返回的东西。

输出：

I have spent hours trying to work this out, found articles online but they don't appear to work.

I have an excel document with data on two tabs and want the below formula to show me the team people are on if the dates and userid match on both forms called "Raised Source" & "Sheet1"

{=INDEX(Sheet1!F:F,MATCH('Raised Source'!E:E&'Raised Source'!F:F,Sheet1!A:A&Sheet1!B:B,0))}

This throws out a #N/A error

Help!!!

解决方案

Firstly I assume that your data is in this format:

And that you are looking to perform the search using data from 'Raised Source'! to search through Sheet1! and return the team from Sheet1! as well.

The formula:

The formula will need to be entered as an array (once copy and pasted while still in the formula bar hit CTRL+SHIFT+ENTER)

=IFERROR(INDEX(Team,MATCH(1,(E1=TeamDate)*(F1=UserID),0)),"")

I have used named ranges to make the formula neater these are:

Team      =Sheet1!$F$1:$F$6
TeamDate  =Sheet1!$A$1:$A$6
UserID    =Sheet1!$B$1:$B$6

Please note that when using an array formula on an entire column calculations slow down considerably so it is best to avoid that and define the exact range you are searching through or overshoot it by a sensible amount if you expect additions.

The Logic:

=INDEX(array,row,[column]) - This formula will 'index' the array you specify, you can tell it a row number and column number you want returned (for example =INDEX(B3:C4,1,1) will return the value in the first row and first column of B3:C4 which is B3. We don't need to tell the formula a column if we just want the first column returned or if the data we are looking for results in is only 1 column wide.

This is where the formula comes in. We use the MATCH(lookup_Value,Lookup_array,[match_type]) formula to tell us what position (or what row in this case) the value we are looking for occurs.

Normal use of MATCH() would be matching exact cell contents within a range but as we are using an array formula I played a little trick by using MATCH(1,(E1=TeamDate)*(F1=UserID),0)

So, I have told the formula to find 1 and tell me the position that it occurs. 1 is returned when both criteria match as with Excel formula, TRUE represents 1 and FALSE represents 0. When this formula works through row by row, if both results match and return TRUE then the array will store 1*1 which is 1 and of course if either or both are FALSE then it will return 1*0, 0*1 or 0*0 all of which result in 0.

As the named ranges all start on the same row, the relative position (or how many times until a 1 appears) will always be the row number that you want the INDEX() formula to return.

IFERROR(value,value_if_error) - If no match is found, the formula will throw an error as it is unable to match 1 to the array that I have built. If this occurs I have told the formula that I would like it to return ,"") instead which is a blank cell. You can update this to any text you would prefer, perhaps ,"Not Found")?

The other IF() in the formula from the other answer was specific to that question as they only wanted to know that there was a match rather than return something from that row.

Output:

这篇关于多标准索引匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！