Return multiple rows with various column data using Vlookup or INDEX-MATCH

Question

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.

Sheet1

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

Sheet2

Philosophy  (Lookup)

Name    Gender  Age

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

Thank you!!

If you need all instances, you would need VBA. Formula is not sufficient. — findwindow
I don't really know about VBA, is there any code that could be apply on this case directly? — Joseph Lin
Then mock up some data that is in the same format and show what your expected outcome would be with that mock data. — Scott Craner
Is "Math, Physics, Chemisty, Literature" all in the same cell, in column A? — BruceWayne

David J. Myers David J. Myers · Accepted Answer · 2016-05-06T02:13:50

NON-ARRAY SOLUTION

Sheet1
- Enter table data on Sheet1 as shown here:
Sheet1 Thumbnail Image

Sheet2
- Cell A1 contains the lookup value: Philosophy.
- Cells A2:C2 contain the header row data: Name Gender Age
- In cell A3, insert this formula:
=INDEX(Sheet1!$A2:$D2,MATCH("*"&$A$1&"*",Sheet1!$A2:$A2,0),MATCH(A$2,Sheet1!$A$1:$D$1,0))
- Press ENTER.
- Copy across to C3, then down to C6 to get the result shown here:
Sheet2 Thumbnail Image

Return multiple rows with various column data using Vlookup or INDEX-MATCH

2 Answers

NON-ARRAY SOLUTION

ARRAY SOLUTION