Excel: match two columns and output third ... AND... there are multiple instances in each column

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.

Solution 1:

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.