Can this be done? Vlookup?

[Amigo602]

I need some help. Based on the following information here is what I need.


A1: ................... B1:
Agent name.......ACD Calls

A2:
Joe Smith


A5: .........................B5:
Agent Summary.........100

I need a formula that finds the agent name Joe Smith then finds "Agent Summary" in the same column and then pulls the ACD column.

I'm not sure it's even possible.

Thanks!

 

[Aladin Akyurek]

The sample looks incomplete... Care to post a sample with two agent names?

 

[Amigo602]

Sure thing....

On another sheet I have all of my agent names.
So what I envisioned was a lookup that looks for Joe Smith, drops down to Agent Summary then pulls one to the right.
So the formula would have two lookup values? (the agent name and "Agent Summary")
All of it would be based off the agent name on the other sheet.
Some employees will have less days worked as you can see with Jane Smith.

I hope that helps.

A ............................B
<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} .font5 {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {mso-number-format:"Short Date";} ruby {ruby-align:left;} rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-char-type:none; display:none;} --> </style> <table style="border-collapse: collapse; width: 180px; height: 290px;" border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="mso-width-source:userset;mso-width-alt:3291" width="90"> <col width="75"> </colgroup><tbody><tr height="13"> <td height="13" width="90">1:Joe Smith</td> <td width="75">ACD Calls</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">2:4/1/11</td> <td align="right">50</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">3:4/2/11</td> <td align="right">50</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">4:4/3/11</td> <td align="right">50</td> </tr> <tr height="13"> <td height="13">5:Agent Summary</td> <td align="right">150</td> </tr> <tr height="13"> <td height="13">
</td> <td>
</td> </tr> <tr height="13"> <td height="13">6:Jane Smith</td> <td>
</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">7:4/1/11</td> <td align="right">50</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">8:4/2/11</td> <td align="right">50</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">
</td> <td align="right">
</td> </tr> <tr height="13"> <td height="13">9:Agent Summary</td> <td align="right">100</td> </tr> <tr height="13"> <td height="13">
</td> <td>
</td> </tr> <tr height="13"> <td height="13">10:Chad Smith</td> <td>
</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">11:4/1/11</td> <td align="right">50</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">12:4/2/11</td> <td align="right">50</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">13:4/3/11</td> <td align="right">50</td> </tr> <tr height="13"> <td class="xl24" height="13">14:Agent Summary
</td> <td align="right">150
</td> </tr> </tbody></table>

 

[Haseeb Avarakkan]

Hello, Try

Hope your data is in Sheet1A:B and sheet2 A2 to down you have the Agent names,

B2, copy down....

=INDEX(INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)):Sheet1!$B$65536,MATCH("Agent*",INDEX(Sheet1!A:A,MATCH(A2,Sheet1!A:A,0)):Sheet1!$A$65536,0))

 

[Amigo602]

It worked! Thank you so much!

 

[Aladin Akyurek]

Also...

Sheet2

A1: Jane Smith

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

=INDEX(Sheet1!$B$1:$B$14,MATCH("Agent Summary",
    IF(ROW(Sheet1!$A$1:$A$14)-ROW(Sheet1!$A$1)+1 > 
     MATCH(A1,Sheet1!$A$1:$A$14,0),Sheet1!$A$1:$A$14,"#"),0))