Index/Match

mulholm

New Member
Joined
Jul 2, 2018
Messages
49
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:

Paid Breaks
Name
Break Time AvailableBreak Time Used
<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> </tbody>

Sheet2:

Agent1
OffLinePaid Breaks
Agent2
OffLine
Paid Breaks



<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> </tbody>
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"
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,611
Office Version
365, 2016
Platform
Windows
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Paid Breaks</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Name</td><td style=";">Break Time Available</td><td style=";">Break Time Used</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Agent1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Agent2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Agent7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A3</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$3:A3</font>)>COUNTIF(<font color="Red">Sheet2!$C$2:$C$11,"paid breaks"</font>),"",INDEX(<font color="Red">Sheet2!$A$2:$A$11,SMALL(<font color="Green">IF(<font color="Purple">Sheet2!$C$2:$C$11="paid breaks",ROW(<font color="Teal">Sheet2!$C$2:$C$11</font>)-ROW(<font color="Teal">Sheet2!$C$2</font>)+1</font>),ROWS(<font color="Purple">$A$3:A3</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Copy down

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Agent1</td><td style=";">OffLine</td><td style=";">Paid Breaks</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Agent2</td><td style=";">OffLine</td><td style=";">Paid Breaks</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Agent3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Agent4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Agent5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Agent6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Agent7</td><td style="text-align: right;;"></td><td style=";">Paid Breaks</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Agent8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Agent9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Agent10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />
 

mulholm

New Member
Joined
Jul 2, 2018
Messages
49
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

Well-known Member
Joined
Dec 14, 2016
Messages
2,611
Office Version
365, 2016
Platform
Windows
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.
Code:
=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

New Member
Joined
Jul 2, 2018
Messages
49
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

Well-known Member
Joined
Dec 14, 2016
Messages
2,611
Office Version
365, 2016
Platform
Windows
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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Paid Breaks</td><td style="text-align: right;;">9/7/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Name</td><td style=";">Break Time Available</td><td style=";">Break Time Used</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Agent7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Agent9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A3</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$3:A3</font>)>COUNTIFS(<font color="Red">Table1[header C],"paid breaks",Table1[Date],$B$1</font>),"",INDEX(<font color="Red">Table1[Name],SMALL(<font color="Green">IF(<font color="Purple">Table1[header C]="paid breaks",IF(<font color="Teal">$B$1=Table1[Date],ROW(<font color="#FF00FF">Table1[header C]</font>)-ROW(<font color="#FF00FF">Sheet2!$C$2</font>)+1</font>)</font>),ROWS(<font color="Purple">$A$3:A3</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Name</td><td style=";">header B</td><td style=";">header C</td><td style=";">Date</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Agent1</td><td style=";">OffLine</td><td style=";">Paid Breaks</td><td style="text-align: right;;">9/1/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Agent2</td><td style=";">OffLine</td><td style=";">Paid Breaks</td><td style="text-align: right;;">9/1/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Agent3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">9/1/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Agent4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Agent5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Agent6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Agent7</td><td style="text-align: right;;"></td><td style=";">Paid Breaks</td><td style="text-align: right;;">9/7/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Agent8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Agent9</td><td style="text-align: right;;"></td><td style=";">Paid Breaks</td><td style="text-align: right;;">9/7/2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Agent10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Agent11</td><td style="text-align: right;;"></td><td style=";">Paid Breaks</td><td style="text-align: right;;">9/10/2018</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />
 

mulholm

New Member
Joined
Jul 2, 2018
Messages
49
{=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))))}
 

Watch MrExcel Video

Forum statistics

Threads
1,099,013
Messages
5,466,021
Members
406,461
Latest member
Garrus

This Week's Hot Topics

Top