INDEX MATCH with Multiple Matches

[caitlincole]

Hello -

I'm working with a large spreadsheet consisting of sales data. One column lists the opportunity IDs and another column lists the tags associated with the opportunity (among other fields that are not relevant for this particular question). The catch is that each line item/row has only one tag, so opportunities with multiple tags are listed in multiple rows. In other words, the column with opportunity IDs has many repeated values.

There are 5 particular tags (out of hundreds in the spreadsheet) that are of interest. I'm looking to add a column with a formula that returns a binary result - 1 if the opportunity ID listed in the row is associated with any of the 5 tags anywhere within the table and 0 if it is not. The issue with a typical VLOOKUP is that it stops and returns the value of only the first available match. This formula will need to be able to assess each opportunity ID match in the table and only display a 0 result if every single row with the given ID has zero of the five tags associated with it. Is there some sort of unique INDEX MATCH formula that could achieve this?

Any help is appreciated. Thank you!

 

[Norie]

Couldn't you use COUNTIF to find out how many times the tags of interest appear?

 

[maabadi]

Welcome to MrExcel Message Board.
Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.
This is Example
Press CTRl+Shift+ENTER
Multiple Criteria

=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))

Multiple Match (Replace Nth with Match number from 1 to ...)

=INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),Nth))