Vlookup with multiple results

AceFI

Board Regular
Joined
Apr 20, 2012
Messages
83
hi all.

I have a table similar to;

Lead #Date InReferrerSales RepCustomerStatus
11/8/18Company AAGJoe BlogsActive
214/8/18Company AHBJohn CitizenActive
321/8/18Company BAGSherly GrayDead

<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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,610
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,199
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try changing "Dead" to "Active" in the formula you posted.
 

AceFI

Board Regular
Joined
Apr 20, 2012
Messages
83
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,610
Office Version
  1. 365
Platform
  1. Windows
Just add another IF statment

Code:
[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]
 

Watch MrExcel Video

Forum statistics

Threads
1,108,842
Messages
5,525,174
Members
409,629
Latest member
McGuilliam

This Week's Hot Topics

Top