Use text in one cell to fill in information in other cells in same row (ex. attached)

[beenile]

Hi,

I have found a few threads that should work, but I can't figure out how to make it work for multiple values. I have eight members who are represented by an 8-letter code. There are about 500 rows of contact information for clients and the member code tells me who the client is associated with.

I want to use this to create a merge document that includes the members' full name, title, email address and phone number for each client. I have created a table on the worksheet as follows:

AMATTHEW BTHOMPSO CMCDONAL
Allan Matthew Barb Thompson Colin McDonald
Manager Senior Vice President President
416-999-9990 416-293-2938 403-283-3827
amatthew@email.ca bthompson@email.ca cmcdonald@email.ca

I'd like a formula that would find the 8-letter code and fill in the information for columns EW_name, EW_title, EW_phone, EW_email in the same row.

Any input would be much appreciated. Thanks!