How to pull multiple values

Hi,

Hoping someone might be able to help me with this. Not sure if it is possible.

I have a sheet which shows which consultant is working on a given project in a given week.
As the sheet is organised by project, a consultants name can appear multiple times if they finish on one and move on to another.

On a seperate sheet i am trying to show the availability of the consultants by drawing on the data in the first sheet.
Unfortunately vlookup only gives me the first value and index requires unique values. There is an IF(SUMPRODUCT formula which allows me to get 1 value (Thanks Ron) but wondering if this can be expanded to look up more values.

Attached is an example spreadsheet. I am trying to combine the data from sheet 1 into sheet 2.

Examplev2.xlsx

Any help greatly appreciated.

Hi - I need to expand it to pull more than one value. Currently it is pulling only A from sheet 1, i need it to also pull the other
values from sheet 1 i.e. L, O etc.
Not sure if this is possible.

Thanks Dilipandey. Much appreciated.