Index/Match

[mulholm]

I am trying to find a name from a list of data on a separate sheet and pull it through to a cell on another sheet:

Sheet1:

[TABLE="width: 335"]
<colgroup><col width="166" style="width: 125pt; mso-width-source: userset; mso-width-alt: 6070;"> <col width="140" style="width: 105pt; mso-width-source: userset; mso-width-alt: 5120;" span="2"> <tbody>[TR]
[TD="width: 166, bgcolor: white"]Paid Breaks
[/TD]
[TD="width: 140, bgcolor: #00CCFF"] [/TD]
[TD="width: 140, bgcolor: #00CCFF"] [/TD]
[/TR]
[TR]
[TD="bgcolor: #00CCFF"]Name
[/TD]
[TD="width: 140, bgcolor: #00CCFF"]Break Time Available[/TD]
[TD="width: 140, bgcolor: #00CCFF"]Break Time Used[/TD]
[/TR]
[TR]
[TD="bgcolor: #00CCFF"][/TD]
[TD="bgcolor: #33CCFF"] [/TD]
[TD="bgcolor: #33CCFF"][/TD]
[/TR]
[TR]
[TD="bgcolor: #00CCFF"][/TD]
[TD="bgcolor: #33CCFF"] [/TD]
[TD="bgcolor: #33CCFF"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2:

[TABLE="width: 226"]
<colgroup><col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="194" style="width: 146pt; mso-width-source: userset; mso-width-alt: 7094;"> <tbody>[TR]
[TD="width: 44, bgcolor: transparent"]Agent1
[/TD]
[TD="width: 62, bgcolor: transparent"]OffLine[/TD]
[TD="width: 194, bgcolor: transparent"]Paid Breaks
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Agent2
[/TD]
[TD="bgcolor: transparent"]OffLine
[/TD]
[TD="bgcolor: transparent"]Paid Breaks
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
I have found the below formula which searches for the word "Paid Breaks" and returns "Agent1" to the "Name" column.

Is there a way i can change the formula to then go down the list and return "Agent2" and so on until there are no more values with the word "Paid Breaks"

 

[Scott T]

Book1
	A	B	C	D
1	Paid Breaks
2	Name	Break Time Available	Break Time Used
3	Agent1
4	Agent2
5	Agent7
6
7
8
9
10
11
12
13
14
15
Sheet1

Cell Formulas
Range	Formula
A3	{=IF(ROWS($A$3:A3)>COUNTIF(Sheet2!$C$2:$C$11,"paid breaks"),"",INDEX(Sheet2!$A$2:$A$11,SMALL(IF(Sheet2!$C$2:$C$11="paid breaks",ROW(Sheet2!$C$2:$C$11)-ROW(Sheet2!$C$2)+1),ROWS($A$3:A3))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy down

Book1
	A	B	C
1	Name
2	Agent1	OffLine	Paid Breaks
3	Agent2	OffLine	Paid Breaks
4	Agent3
5	Agent4
6	Agent5
7	Agent6
8	Agent7		Paid Breaks
9	Agent8
10	Agent9
11	Agent10
Sheet2

 

[mulholm]

Thank you, let me elaborate a bit now that you get what i am looking to do:

the location of Paid break on the 2nd sheet will differ depending on how many people are in so will never be a constant. One day it could be C2:C11 but next day it could be C26:C34.
The names i am trying to pull through to sheet1 will always be in column a and will always be in the corresponding row to the paid break.
Hope that makes sense, if you can help at all?

 

[Scott T]

If it always starts from the top and just expands you could turn the data in sheet2 into an excel table and use table formula nomenclature to refer to the columns. As you add data to the bottom of the table it was automatically expand and.

=IF(ROWS($A$3:A3)>COUNTIF(Table1[header C],"paid breaks"),"",INDEX(Table1[Name],SMALL(IF(Table1[header C]="paid breaks",ROW(Table1[header C])-ROW(Sheet2!$C$2)+1),ROWS($A$3:A3))))

If you need look at different ranges in Column C then what determines the range?

 

[mulholm]

It's dependent on how many agents I would I have in.

For example yesterday paid breaks appeared from C62:C68 but the day before it was C56:C62
the code you gave me above works in relation to if paid breaks was a constant but not for if it moves row everyday.

 

[Scott T]

There would need to be something in the data set that could be used to know if the data should be used or not. Like a date

For example will only list if it has Paid breaks and is the same date as in B1

Book1
	A	B	C	D
1	Paid Breaks	9/7/2018
2	Name	Break Time Available	Break Time Used
3	Agent7
4	Agent9
5
Sheet1

Cell Formulas
Range	Formula
A3	{=IF(ROWS($A$3:A3)>COUNTIFS(Table1[header C],"paid breaks",Table1[Date],$B$1),"",INDEX(Table1[Name],SMALL(IF(Table1[header C]="paid breaks",IF($B$1=Table1[Date],ROW(Table1[header C])-ROW(Sheet2!$C$2)+1)),ROWS($A$3:A3))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C	D
1	Name	header B	header C	Date
2	Agent1	OffLine	Paid Breaks	9/1/2018
3	Agent2	OffLine	Paid Breaks	9/1/2018
4	Agent3			9/1/2018
5	Agent4
6	Agent5
7	Agent6
8	Agent7		Paid Breaks	9/7/2018
9	Agent8
10	Agent9		Paid Breaks	9/7/2018
11	Agent10
12	Agent11		Paid Breaks	9/10/2018
Sheet2

 

[mulholm]

{=IF(ROWS($A$3:A3)>COUNTIFS(Table1[header C],"paid breaks",Table1[Date],$B$1),"",INDEX(Table1[Name],SMALL(IF(Table1[header C]="paid breaks",IF($B$1=Table1[Date],ROW(Table1[header C])-ROW(Sheet2!$C$2)+1)),ROWS($A$3:A3))))}


I think it is the highlighted bit of formula that is making it not work as it is not always going to be C2. It could be anywhere in the C column depending on who is in.


What i ideally need is the below code that you gave me which worked if specific rows but need it altered to search for "Paid Breaks" in the whole of column "C":
{=IF(ROWS($A$3:A3)>COUNTIF(Sheet2!$C$2:$C$11,"paid breaks"),"",INDEX(Sheet2!$A$2:$A$11,SMALL(IF(Sheet2!$C$2:$C$11="paid breaks",ROW(Sheet2!$C$2:$C$11)-ROW(Sheet2!$C$2)+1),ROWS($A$3:A3))))}