Pulling multiple items using Index/Match with a wild card search

mattebe

New Member
Joined
Jun 30, 2015
Messages
1
I appreciate any help anyone can offer.

Here is what I'm trying to do. I need to compare two worksheets with the goal of identifying all the occurances of data from one sheet and pulling it into another. Here is an abbreviated example.

ReferenceSheet


A
1
acetaminophen 325mg tablet
2
acetaminophen 500mg tablet
3
acetaminophen 160mg/10.5ml Solution
4
doxepin 10mg capsule
5
doxepin 25mg capsule
6
dopamine IV infusion

<TBODY>
</TBODY>


WorkSheet

A
B
C
D
1
acetaminophen
2
doxepin
3
dopamine
4
aspirin
5
6

<TBODY>
</TBODY>

What I would like to see happen within the worksheet:

In Row 1, Column B, C, D-each of the three acetaminophen entries from reference table
In Row 2 Column B and C-each of the two doxepin entries from reference table
In Row 3 Column B-the Dopamine entry from reference table
In Row 4 column B-N/A

I have the following equation in the column B which will pull the first insance of the item into the file, but I don't know how to pull the multiple entries. Here is an example of the current formula I would have in worksheet cell B1

=INDEX('referencesheet'!$A$1:$a$5,MATCH("*"&Worksheet!A1&"*",'referencesheet'!$A$1:$A$5,0))
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,215,186
Messages
6,123,537
Members
449,106
Latest member
techog

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
Back
Top