Return Multi Value using 2 criteris

[Arun R]

Hi,

A sample excel is attached. Need to return all names matching "Team" and "Source". Any suggestions is appreciated.

Thanks,
Arun

 

[Nebu]

Hi:

Use the following formula
=IFERROR(INDEX($A$2:$A$7,AGGREGATE(15,6,ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7)-1)/(($F$2=$B$2:$B$7)*($G$2=$C$2:$C$7)),ROW(A1))),"")

Thanks

 

[Arun R]

Thanks Nebu. I tried but it seems not working. i have total 1000 rows, so i changed 6 in formula above to 1000. No values are returned.

Also data is in a Master sheet and i need it in different sheet if that makes any difference.

My formula is looking like:

=IFERROR(INDEX(Final_Data!$A$2:$A$1000,AGGREGATE(15,1000,ROW(Final_Data!$A$2:$A$1000)-MIN(ROW(Final_Data!$A$2:$A$1000)-1)/(($F$1=Final_Data!$K$2:$K$1000)*($G$1=Final_Data!$M$2:$M$1000)),ROW(B7))),"")

 

[Nebu]

Hi:

The formula you have given is wrong,
=IFERROR(INDEX(Final_Data!$A$2:$A$1000,AGGREGATE(15,1000,ROW(Final_Data!$A$2:$A$1000)-MIN(ROW(Final_Data!$A$2:$A$1000)-1)/(($F$1=Final_Data!$K$2:$K$1000)*($G$1=Final_Data!$M$2:$M$1000)),ROW(B7))),"")

The 1000 is not no: of rows , the 6 I have given is the option for aggregate formula to ignore error values, change 1000 in your formula back to 6.

Thanks