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"
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,593
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,593
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,593
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))))}
 

Forum statistics

Threads
1,082,298
Messages
5,364,377
Members
400,795
Latest member
Vercas

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top