Return multiple rows with various column data using Vlookup or INDEX-MATCH
<p>Now I have an excel file with 2 sheets, I need to pull some data from sheet1 to sheet2, based on a single criterion - Text in Column A from sheet1 contains certain text string from A2 from sheet2. In the meantime, I also have to pull data from other columns from those rows where the text string is found. However, Vlookup only returns the first instance. Since the file is confidential, I created a moackup for this.</p>

<p>Sheet1</p>

<pre><code>Classes Taken   Name    Gender  Age

Math,Physics,Chemistry, Literature  Joseph  Male    16

Chinese, History, Philosophy, Politics  Alice   Female  17

Philosophy, Math, Physics   Erica   Female  16

Chinese, Biology, Statistics    Evan    Male    18
</code></pre>

<p>Sheet2</p>

<pre><code>Philosophy  (Lookup)

Name    Gender  Age
</code></pre>

<p>What if I want the information of people who have taken philosophy class displayed in sheet2.</p>

<p>Thank you!!</p>