Return multiple values for a lookup

[lracrft]

I realize that this is a repeat of other posts that are out there but I have tried for hours to tweak the formula suggested in the other posts to fit my needs and still can't quite get there. I have a name in cell C3 (Sheet2) and I want to return all the values in column A of Sheet 1 where there is a match in column B of Sheet 1 to the name in Sheet2 cell C3. I want the resulting matches to appear in Sheet2 B13:B50 (this would eventually be an expanding range). The formula I have used is:

{=INDEX('Contact Log'!$A$2:$B$999,SMALL(IF('Contact Log'!$B$2:$B$99=$C$3,'Contact Log'!$A$2:$A$999),ROW('Client Summary'!$B$13:B13)),-1)}

When I get this to work I will add in the IF(ISERROR portion to return blanks if there are no more results, but I just know I need to get the base formula to work first. Can anyone help with this?

 

[wideboydixon]

Book1
	A	B
2	1	a
3	2	b
4	3	a
5	4	b
6	5	a
7	6	b
Contact Log

Book1
	B	C
3		a
4
5
6
7
8
9
10
11
12
13	1
14	3
15	5
Client Summary

Cell Formulas
Range	Formula
B13	{=IFERROR(INDEX('Contact Log'!$A$2:$A$999,SMALL(IF('Contact Log'!$B$2:$B$999=$C$3,ROW('Contact Log'!$B$2:$B$999)-ROW('Contact Log'!$B$2)+1),ROWS('Client Summary'!$B$13:$B13))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Scott T]

In the value of true of the IF formula you need to use ROW to get a series of number starting at 1 you need to then subtract the starting row and then add one. For the K value in the small you need to use ROWS. The IF logical test had different dimensions from your array form INDEX.. The Array used in index does not need both columns you can only include column A and then you do not need to specify the column argument.

=IF(ROWS('Client Summary'!$B$13:B13)>COUNTIF('Contact Log'!$B$2:$B$999,'Client Summary'!$C$3),"",INDEX('Contact Log'!$A$2:$A$999,SMALL(IF('Contact Log'!$B$2:$B$999=$C$3,ROW('Contact Log'!$A$2:$A$999)-ROW('Contact Log'!$A$2)+1),ROWS('Client Summary'!$B$13:B13))))

 

[lracrft]

@wideboydixon Thank you! That worked like a charm! Now I have to study it to understand what you did differently so I can re-create in the future.