Vlookup with multiple results

[AceFI]

hi all.

I have a table similar to;

Lead #	Date In	Referrer	Sales Rep	Customer	Status
1	1/8/18	Company A	AG	Joe Blogs	Active
2	14/8/18	Company A	HB	John Citizen	Active
3	21/8/18	Company B	AG	Sherly Gray	Dead

<tbody>
</tbody>

I'm wanting to have sheet 2 display on deals for Company A that are active..
I have tried;
{IFERROR(INDEX('Data Sheet'!A:A,SMALL(IF('Data Sheet'!$C:$C="Company A",IF('Data Sheet'!$F:$F="Dead",ROW($A:$A))),ROWS($A$1:A1))),"")}
and drag down, however not getting any data coming accross?

thanks

 

[AhoyNC]

If you want ACTIVE why does your formula look for DEAD. Since there is no DEAD status for Company A you will get all blanks in your example.
It's also not a good idea to use whole column ranges with array formulas as it can slow things down as the IFERROR functiom is going to check all 1 million plus rows even if there is no data in them.

 

[JoeMo]

Try changing "Dead" to "Active" in the formula you posted.

 

[AceFI]

duh.. changed it to active and it worked haha

thanks all.

If i'm wanting to add another identifier in the next right column to be "Tag" with options A B C, how would i add this to the formula for all "A" 's

thanks

 

[AhoyNC]

Just add another IF statment

[TABLE="width: 1137"]
<colgroup><col width="1137"></colgroup><tbody>[TR]
   [TD="width: 1137"]=IFERROR(INDEX('Data   Sheet'!A:A,SMALL(IF('Data Sheet'!$C:$C="Company A",IF('Data   Sheet'!$F:$F="Active"[COLOR=#ff0000],IF('Data   Sheet'!$G:$G="Tag"[/COLOR],ROW($A:$A)))),ROWS($A$1:A1))),"")
[/TD]
 [/TR]
</tbody>[/TABLE]