Need help with Index + Match Multiple Results

xenohadden said:

I'm trying to product a list of people who haven't managed to get any lead for a month, I have some examples I have used for the purposes of testing (not the actual figures).

<code style="margin: 0px; padding: 0px; font-style: inherit;">Team Leader No of Leads No of Sales Steve 12 6 Claire 8 4 Tim 8 3 Total 28 13</code>
I can get the following code to display the results I want, however it will only display one person, even if there are multiple people on zero:

=INDEX('[Team sheet.xlsx]Steves Team'!$B$5:$B$14,MATCH(0,'[Team sheet.xlsx]Steves Team'!$C$5:$C$14,0))

Anyone able to help?

Click to expand...

=SUM(IF('[Team sheet.xlsx]Steves Team'!$C$5:$C$14=0,1))

=IF(ROWS($A$2:A2)<=$A$1,INDEX('[Team sheet.xlsx]Steves Team'!$B$5:$B$14,
  SMALL(IF(MATCH('[Team sheet.xlsx]Steves Team'!$C$5:$C$14=0,
   ROW('[Team sheet.xlsx]Steves Team'!$C$5:$C$14)-
    ROW('[Team sheet.xlsx]Steves Team'!$C$5)+1),ROWS($A$2:A2))),"")

xenohadden said:

I'm trying to product a list of people who haven't managed to get any lead for a month, I have some examples I have used for the purposes of testing (not the actual figures).

Rich (BB code):

Sales Person    No of Leads     No of Sales 
Joseph               12     6    
John        8     4    
Josh    8     3    
Jamie    9     5    
Jackie    3     3    
Johnson    10     2    
Jonathan    0     -      
Jagjit    7     1    
Jairam    0     4    
Total    57    28

<CODE style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; MARGIN: 0px; PADDING-TOP: 0px"></CODE><CODE style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; MARGIN: 0px; PADDING-TOP: 0px">

</CODE>
I can get the following code to display the results I want, however it will only display one person, even if there are multiple people on zero:

=INDEX('[Team sheet.xlsx]Steves Team'!$B$5:$B$14,MATCH(0,'[Team sheet.xlsx]Steves Team'!$C$5:$C$14,0))

Anyone able to help?

Click to expand...

T. Valko said:

Try this...

Sheet1

	A	B	C	D	E
1	Sales Person	No of Leads	_	Count	Sales Person
2	Joseph	12	_	2	Jonathan
3	John	8	_	_	Jairam
4	Josh	8	_	_	_
5	Jamie	9	_	_	_
6	Jackie	3	_	_	_
7	Johnson	10	_	_	_
8	Jonathan	0	_	_	_
9	Jagjit	7	_	_	_
10	Jairam	0	_	_	_
11	Total	57	_	_	_

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:77px;"><col style="width:85px;"><col style="width:29px;"><col style="width:45px;"><col style="width:77px;"></colgroup><tbody>
</tbody>

Enter this formula in D2. This will return the count of records that meet the criteria.

=SUMPRODUCT(--(B2:B11=0))

Enter this array formula** in E2:

=IF(ROWS(E$2:E2)>D$2,"",INDEX(A:A,SMALL(IF(B2:B11=0,ROW(B2:B11)),ROWS(E$2:E2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Click to expand...

Aladin Akyurek said:

A1, control+shift+enter, not just enter:

Rich (BB code):

=SUM(IF('[Team sheet.xlsx]Steves Team'!$C$5:$C$14=0,1))

A2, control+shift+enter, not just enter, and copy down:

Rich (BB code):

=IF(ROWS($A$2:A2)<=$A$1,INDEX('[Team sheet.xlsx]Steves Team'!$B$5:$B$14,
  SMALL(IF(MATCH('[Team sheet.xlsx]Steves Team'!$C$5:$C$14=0,
   ROW('[Team sheet.xlsx]Steves Team'!$C$5:$C$14)-
    ROW('[Team sheet.xlsx]Steves Team'!$C$5)+1),ROWS($A$2:A2))),"")

Click to expand...

Aladin Akyurek said:

Did you miss post#2?

Click to expand...

T. Valko said:

Post #3 is a better solution.

Click to expand...

xenohadden said:

What do you mean copy down until I get blanks? The idea is to produce a list of all people who have 0 leads, benefit the table automatically, if I have to drag the formula down it defeat the purpose of what i'm trying to achieve :P

Also what is the count columns for? I'd don't get its purpose, when i tried it it said 1 on my sheet :/

Appricate the help btw, sorry if I'm being stupid!

Click to expand...