Using data from one table to select data from another table

[CandyT]

I need to take a list of names in one table (Table A) and search for that list in another table (Table B). The names can exist in any of several columns of names in Table B. I need to start at the right-most column of Table B and search each column, by row, to the left until I find the matching name. When I find the matching name, I need to append that name to the row in a brand new column in Table B.

For example:

My list of names in Table A is this:

Jan Smith
Joe Brown
John Carter
Bev Jones

Table B has four columns of names. Starting at the left I want it to search the row for a match for "Jan Smith". When it finds it, I want it to write the name back to Table B, in a new column. The tricky part is that it has to start at the right and work left and then stop the first time it finds a match to a name in Table A. The reason is that it may find "Jan Smith" in the 2nd column from the left AND find the "Joe Brown" in the same row in the 3rd column from the left. The correct data to return is "Jan Smith" because it is the first one found from right to left. It should ignore the second match.

Any suggestions?

Candy T