Extract all rows from a range that have specific text from lookup

[Davecliff]

Hi all,

I have an excel file with multiple rows and columns used for planning jobs. The columns contain job descriptions, job numbers, Worker names etc. Few jobs require 2 people to complete and have 2 names on them ex: Luke and Mike.

Out of all the jobs i want to extract the jobs for specific people, who are in the lookup sheet.

Excel file structure:
Columns A-J in main sheet has all data with job number, description, name etc.., with worker names are in column D.
Lookup sheet A1:A10 contain the specific people.
The job number, description and other columns have to be extracted onto a new sheet for these people in lookup table. I have been using the below formula on the new sheet which works for individual names. However, does not recognise if they are matched with others (combined jobs). The formula has to work even if they are mixed with others. I tried using wildcard to the below formula but was not able to get any result.

I've been breaking my head since yesterday. Any help on this issue would be greatly appreciated.

=INDEX('Day Shift'!$A$3:$J$100, SMALL(IF(COUNTIF(Lookup!$A$1:$A$10,'Day Shift'!$D$3:$D$100), MATCH(ROW('Day Shift'!$A$3:$J$100), ROW('Day Shift'!$A$3:$J$100)), ""), ROWS(A1:$A$1)), COLUMNS($A$2:A2))

 

[COwen]

Sounds simple enough. Can you share a larger sample of your data or a link to a copy of your workbook?

 

[Davecliff]

Hi cowen, sample data of the dataset is in this excel file Filebin :: bin usgcuudlm9vutxa8

Thank you