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 show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,215,950
Messages
6,127,897
Members
449,411
Latest member
AppellatePerson

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