VLOOK UP - Multiple Corresponding Values

[kkimiri]

In the attached excel, in the staff list worksheet, how can i look up the person from the data worksheet to return the vales highlighted (i.e to have the look up formula in the staff worksheet ignore the false & Value returns from the data worksheet and pick the highlighted values and return the same)

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Syedali]

Hi ,

See if this is OK.

Narayan

Hi Narayan,

I have one question for my understanding this formula don't mistake me.

1. Is this only pick the answer from 4th row in Data sheet.
2. If i change E4 cell (Make it "FALSE")in data sheet and E5 Cell i changed
GCP/KEN/085/EC this but the formula not working.

Once again don't mistake me ... Thanks in Advance

 

[NARAYANK991]

Hi Narayan,

I have one question for my understanding this formula don't mistake me.

1. Is this only pick the answer from 4th row in Data sheet.
2. If i change E4 cell (Make it "FALSE")in data sheet and E5 Cell i changed
GCP/KEN/085/EC this but the formula not working.

Once again don't mistake me ... Thanks in Advance

Hi ,

You are right ; see the file for the corrected formula. Thanks.

Narayan

 

[Syedali]

Hi ,

You are right ; see the file for the corrected formula. Thanks.

Narayan

Thank you now am trying to understand your formula.....

 

[bosco_yip]

Another option and in non-array formula solution.

In D4, formula copied across :

=INDEX(DATA!$E$3:$V$100,AGGREGATE(15,6,ROW(DATA!$C$3:$C$100)-ROW(DATA!$C$2)/($B4=DATA!$C$3:$C$100)/ISTEXT(INDEX(DATA!$E$3:$V$100,,MATCH(D$3,DATA!$E$2:$V$2,0))),1),MATCH(D$3,DATA!$E$2:$V$2,0))

Regards
Bosco

 

[kkimiri]

Hi ,

You are right ; see the file for the corrected formula. Thanks.

Narayan

Thank You soo much!! It works well

 

[kkimiri]

Another option and in non-array formula solution.

In D4, formula copied across :

=INDEX(DATA!$E$3:$V$100,AGGREGATE(15,6,ROW(DATA!$C$3:$C$100)-ROW(DATA!$C$2)/($B4=DATA!$C$3:$C$100)/ISTEXT(INDEX(DATA!$E$3:$V$100,,MATCH(D$3,DATA!$E$2:$V$2,0))),1),MATCH(D$3,DATA!$E$2:$V$2,0))

Regards
Bosco

Thanks A Lot!! this also works perfect. Appreciated