INDEX MATCH with Multiple Matches

caitlincole

New Member
Joined
Jan 25, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
Couldn't you use COUNTIF to find out how many times the tags of interest appear?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,555
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Excel Formula:
=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 ...)
Excel Formula:
=INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),Nth))
 

Forum statistics

Threads
1,143,620
Messages
5,719,788
Members
422,244
Latest member
AYSHANA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top