Solved: Conditional Worksheet Column Comparison and Text Insert

[Sisu]

Hi,

Is it possible to compare a column of names in one worksheet (A) with a column of names in another worksheet (B) and if it exists in both, insert text into a column on the sheet (B)? I currently have one sheet named Payments with only names of people who made payments stored in column A:2 down and another sheet named Group of many people A:5 and down. I would like for the word “Paid” to be placed in Group column D of a groupmember whose name exists in payments. Is it possible to do this? I would sincerely appreciate any help.

Thank you,
Sisu

[Chabu]

The easiest way (without VBA) is to use the VLOOKUP function

In column D:5 of the group sheet put the following formula.
=VLOOKUP(A5;Payments!A:A;1;FALSE)
Then copy it downward for as far as there are names in column A.

Greetings
Bruno

[Chabu]

probably "," instead of ";" for you

[Sisu]

I already have a three part IF(NUMBER formula in the destination cell that has its input based on the status listed in an adjacent cell. Ex if b is deceased then d is na. Can I add the lookup formula to this formula even though the original handles everything in the same sheet. Thanks.

[Chabu]

I don't see why not. What is the other formula?

[Sisu]

=IF(ISNUMBER(SEARCH("Associate",B6)),"Unpaid",IF(ISNUMBER(SEARCH("Graduate",B7)),"N/A",IF(ISNUMBER(SEARCH("Deceased",B6)),"N/A",IF(ISNUMBER(SEARCH("Emeritus",B6)),"N/A"))))

[Chabu]

would you mind sending the file (or an example version)?

[Sisu]

Chabu,

Here is an example spreadsheet: Note in the column with the formula in the second sheet.

[Chabu]

Why do you use search? Are the text values (like "Associate" part of longer text strings?
If not then use data validation to restrict the values to the list you expect and simplify your formula (=IF(B6="Associate"...)
What is the logic to fit in the VLOOKUP? (What do you want to achieve?

send your sheet if possible.

Greetings

[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!!

[Sisu]

Hi All,

Yes Shriva this has something to do with academia. Yours and Chabu's code has helped.

I realize now that what I am trying to do will not solve other issues I'm having with the same workbook. It is only a small piece of what must be done for this. For better or worse I am exclusively learning this language for some time. I will post back with a reply to my own question when I solve it. It may be some time until this happens. The thread will be deleted marked solved for now.