Solved: Conditional Worksheet Column Comparison and Text Insert

[shrivallabha]

Sisu,
It looks to me that you have over-complicated things! In a non-Associate case all you want is N/A (Not Applicable) so you want to check in the case of Associate. If the associate's name appears in the payment sheet then payment is made. The following formula works for me (at least for all the cases you have given)

=IF(B6<>"Associate","N/A",IF(ISERROR(VLOOKUP(A6,Payments!A:A,1,0)),"Unpaid","Paid"))

Just a side question, not really important, does it pertain to professors / teaching? Last time I saw the Emeritus was (13 years back) on my Chemical Engineering coursebook....Professor Emeritus at Cornell University and the name of the book: Unit Operations of Chemical Engineering!

PS: Chabu's suggestion of adding Data Validation is really good, you should look into it. Saves headaches!!