Return a Value When A Criterion is Met

[12learnexcel]

Hi experts,

I searched the whole forum for this type of query and found no solution (or I may not be using the correct keywords).

Kindly help me create a formula wherein whenever I add a sales entry from a certain client in sheet 2 (sales worksheet), the respective agent's name will automatically be reflected under the "agent" column. Source data is located in sheet 1 (Masterlist).

Thank you in advance.


<table x:str="" style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" height="445" width="380"><col style="width: 48pt;" width="64"> <col style="width: 70pt;" width="93"> <col style="width: 56pt;" width="75"> <col style="width: 272pt;" width="363"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" height="20" width="64">Masterlist (Sheet 1)</td> <td class="xl65" style="border-left: medium none; width: 70pt;" width="93">
</td> <td class="xl65" style="border-left: medium none; width: 56pt;" width="75">
</td> <td class="xl65" style="border-left: medium none; width: 272pt;" width="363">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Client No</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Client</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Agent</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Succeeding Columns contain Other Info about the Client</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">1</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Cathy</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Joel</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">2</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jack</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">3</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Mary</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jill</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">4</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Joe</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jack</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Peter</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jill</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" x:num="" align="center" height="20">6</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Jack</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" height="20">Sales Worksheet (Sheet 2)</td> <td class="xl65" style="border-left: medium none;">
</td> <td class="xl65" style="border-left: medium none;">
</td> <td class="xl65" style="border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="border-top: medium none; height: 15pt;" height="20">Date</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Client</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Agent</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Sales</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40023" align="center" height="20">29-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">98</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40023" align="center" height="20">29-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Mary</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">120</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40023" align="center" height="20">29-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Peter</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">25</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40024" align="center" height="20">30-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Mary</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">75</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40024" align="center" height="20">30-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Cathy</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">35</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="border-top: medium none; height: 15pt;" x:num="40025" align="center" height="20">31-Jul</td> <td class="xl65" style="border-top: medium none; border-left: medium none;">Peter</td> <td class="xl65" style="border-top: medium none; border-left: medium none;"> ?</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" x:num="">80</td> </tr> </tbody></table>

 

[ravishankar]

Hi
Vlookup function suits your case. In col C of sheet2 insert the formula
C3=vlookup(B3,Sheet1!$B$2:$C$25,2,false).Replace 25 with last row number and copy it down to all rows.
Ravi

 

[12learnexcel]

Gee Ravi. Thanks. It worked. vlookup is something new for me. Thank you so much.