Index Match + multiple criteria + return list/range

Question

I am trying to look things up in a table and return them with a formula, with the following requirements:

1. There are two criteria (columns) to include in the look-up
2. Need to return the list of all matches rather than a single item

I have found ways of doing these things on their own (using more complex Index Match for #1 and using the “:” operator for #2 to specify a range and return a list in the form of an array), but whenever I try to combine them it doesn’t work.

I have shared an example below, where ‘Table 1’ is the source data and ‘Our selections’ are the references for looking up. Can someone please let me know a formula that can go in the mentioned cell that will return the green ‘Returned list’ of associated entries from the ‘Interaction’ column (of Table 1)?

I am running a Mac with Excel version 16.46 Thanks in advance!

image

Answer 1

The following page explains a similar case, only it's not returning an array. It works with the index. So you would have to put the formula in enough rows to show all matches: https://www.xelplus.com/return-multiple-match-values-in-excel/

In your case the formula would be something like this:

=IF(ROWS($A$2:A2)<=COUNTIFS($A$2:$A$18,$G$2,$B$2:$B$18,$G$3),INDEX($C$2:$C$18;AGGREGATE(15,3,(($A$2:$A$18&$B$2:$B$18=$G$2&$G$3)/($A$2:$A$18&$B$2:$B$18=$G$2&$G$3)*ROW($A$2:$A$18))-ROW($A$1),ROWS($E$2:E2))),"")

Answer 2

It's unclear what column/row your data starts/ends, but I assumed your table starts at A2, you project selection is in E2 and your filter starts at E6

In that case use the following array formula in E6 and drag down: =IFERROR(INDEX(C:C,MATCH(1,(A:A=E$2)*(B:B=E$3)*(COUNTIF(E$5:E5,C:C)=0),0)),"") Array formulas are to be entered with ctrl+shift+enter

If you where to have office 365 this formula would do the trick: =FILTER(C:C,(A:A=E$2)*(B:B=E$3)*(C:C<>"")) this will adjust to the number of occurrences automatically.