Excel：匹配两列并输出第三个... AND ...每列中有多个实例

问题描述

处理以前的帖子：

E6中的公式是

  = IFERROR（INDEX（C $ 1：C $ 99，积分（15,6，ROW（$ 1：$ 99）/（（A $ 1： A $ 99 =red）*（B $ 1：B $ 99 =boat）），ROW（1：1））），）
  

您实际上正在使用小的AGGREGATE功能的子功能，所以你可以得到第二，第三等连续的匹配增加 k 参数。我通过使用等于1的 ROW（1：1）完成上述操作，但会随着公式的填充而增加到2,3等。

Working off a previous post:Excel match two columns and output third

I have values in column A that are not unique and values in column B that are not unique, but together column A and B produce unique combinations:

     A     B     C
1   Red   Car   Result#1

2   Blue  Boat  Result #2

3   Red   Boat  Result #3

4   Green Car   Result #4

Let's say I want to find a match where Column A = Red and Column B = Boat which should return the corresponding value in Column C which should be Result #3.

Using the previous post's solution:

=IF(MATCH("Red",A1:A4,0)=MATCH("Boat",B1:B4,0),INDEX(C1:C4,MATCH("Boat",B1:B4,0)),0)

This would actually return value the first match for Boat in column B which would be result#2 rather than the intended result#3 where the match was true.

Any ideas on how to modify or write a function that would specify to retrieve information relative to specifically where the match was true (without using VBA)?

I've thought of a possible work around by creating another column that combines Col A and B to make a unique identifier but I was hoping to avoid that.

Thanks! Really appreciate it and sorry about the table formatting. I'm still very new at this.

解决方案

You can retrieve a two column match using the AGGREGATE function to force anything that does not match into an error and ignore the errors.

      

The formula in E6 is,

=IFERROR(INDEX(C$1:C$99,AGGREGATE(15,6,ROW($1:$99)/((A$1:A$99="red")*(B$1:B$99="boat")), ROW(1:1))), "")

You are actually using the SMALL sub-function of the AGGREGATE function so you can get the second, third, etc. successive matches by increasing the k paramter. I done this above by using ROW(1:1) which equals 1 but will increase to 2, 3, etc as the formula is filled down.

这篇关于Excel：匹配两列并输出第三个... AND ...每列中有多个实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！