Vlookup with multiple results

[AceFI]

hi all.

I have a table similar to;

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Lead #[/TD]
[TD]Date In[/TD]
[TD]Referrer[/TD]
[TD]Sales Rep[/TD]
[TD]Customer[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1/8/18[/TD]
[TD]Company A[/TD]
[TD]AG[/TD]
[TD]Joe Blogs[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]14/8/18[/TD]
[TD]Company A[/TD]
[TD]HB[/TD]
[TD]John Citizen[/TD]
[TD]Active[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]21/8/18[/TD]
[TD]Company B[/TD]
[TD]AG[/TD]
[TD]Sherly Gray[/TD]
[TD]Dead[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]