Finding multiple related contacts using VLOOKUP/INDEXMATCHING and returning phone number, email etc.

[moodoggy]

Hi everyone,

I just wanted to say thank you in advance for your time and help.

I have been tasked with sorting through 4,000 contacts and finding the telephone and mobile numbers and emails of any contacts that are related to a certain Account from a great list of 450. This relationship is determined by either direct reference to a certain Account Name in the Accounts or a cell in the FullName, Job Title, Organisation or notes column contains the Account Name in it.

I have tried VLOOKUP and MATCHING and INDEXING but have not been able to get it to find what I need. The primary issue is that 5 different contacts may be linked to one single Account (either found in their job title, organisation or whatever), and therefore I need to return multiple phone numbers, emails and other information that are linked to the 5 different contacts. VLOOKUP only found one contact and I have tried many different MATCHING and INDEXING solutions from examples and help sites but cannot get it to work.

Please see an example template of my contacts and the Accounts tab. There are obviously more and this data is fake.

ACCOUNTS	FirstName	LastName	FullName	Account Name	Job Title	Organisation	Notes	Phone	Email
Aboud	Chris	Armstrong	Chris Armstrong	Redley	CEO at Capers Ltd	Capters		04 3738 9873	test@gmail.com
Andrews	Christine	Aarons	Christine Aarons					5 3738 9873	test@gmail.com
Frazer	Steven	Aboud	Steven Aboud					6 3738 9873	test@gmail.com
Andrews	Michael	Abeles	Michael Abeles	Abeles		Littleman Ltd		7 3738 9873	test@gmail.com
Armstrong	Andrew	Fairlight	Andrew Fairlight	Fairlight	Executive Chairman	Abercrom Ltd		8 3738 9873	test@gmail.com
Hunter	Russell	Andrews	Russell Andrews			GEY Ltd		9 3738 9873	test@gmail.com
etc	Allander	Aboud	Allander Aboud					10 3738 9873	test@gmail.com
etc	George	Abraham	George Abraham			Ground Investments	11 3738 9873	test@gmail.com
etc	Chris	Abry	Chris Abry	Abry	Head of Research	Investee Corp		12 3738 9873	test@gmail.com
etc	Menzie	Acharya	Menzie Acharya			Carline Venture		13 3738 9873	test@gmail.com
etc	Josh	Drickaya	Josh Drickaya	Drickaya	Senior Portfolio Manager	River Capital		14 3738 9873	test@gmail.com
etc	Robert	Adlee	Robert Adlee	Hargraves	Chief Executive Officer	Parks Investment		15 3738 9873	test@gmail.com
etc	Kim	Dryer	Kim Dryer	Dryer	Office Manager/ EA to Robert Ell	Leda Capital		16 3738 9873	test@gmail.com
etc	Michelle	Hunter	Michelle Hunter	Caster	Personal Assistant to Chairman & CEO	Merene Group		17 3738 9873	test@gmail.com

<tbody>
</tbody>

 

[AliGW]

What are the expected outcomes using your sample data above?

 

[moodoggy]

What are the expected outcomes using your sample data above?

Please first note that the Accounts column is not linked to any rows and is just a LIST of the accounts.

For the output, say "Aboud" is contained in a long paragraph of Michelle Hunter's notes, Allander Aboud and Chris Aboud have the last name "Aboud" and George Abraham is the CEO of "Aboud's" Investment Team (not in the example above but should be). The expected outcome would be for Michelle's, Allander's, Chris's and Georges Full Name to be outputted, and in the same row, have their mobile numbers, telephone numbers and emails outputted.
I can then get in contact with these individuals that are somehow related to the Aboud Account.

 

[AliGW]

There is no notes column in your sample data. What we need is a properly REPRESENTATIVE sample of your raw data AND some expected outcomes in a table. You are leaving too much to our imagination at present.

 

[moodoggy]

There is no notes column in your sample data. What we need is a properly REPRESENTATIVE sample of your raw data AND some expected outcomes in a table. You are leaving too much to our imagination at present.

Sorry for my naivety. First time posting on a Forum to get help with Excel.

Updated Table

ACCOUNTS		FirstName	LastName	FullName	Account Name	Job Title	Organisation	Notes	Phone	Email
Aboud		Chris	Armstrong	Chris Armstrong	Redley	CEO at Capers Ltd	Capters	Principal of Aboud Family	04 3738 9873	Chris .Armstrong@gmail.com
Andrews		Christine	Aarons	Christine Aarons				5 3738 9873	Christine.Aarons@gmail.com
Frazer		Steven	Aboud	Steven Aboud				6 3738 9873	Steven.Aboud@gmail.com
Andrews		Michael	Abeles	Michael Abeles	Abeles		Littleman Ltd	Met with Michael on 13/09/15	7 3738 9873	Michael.Abeles@gmail.com
Armstrong		Andrew	Fairlight	Andrew Fairlight	Fairlight	Executive Chairman	Abercrom Ltd		8 3738 9873	Andrew.Fairlight@gmail.com
Hunter		Russell	Andrews	Russell Andrews	Chairman of Aboud Investments	9 3738 9873	Russell.Andrews@gmail.com
Georgina		Allander	Aboud	Allander Aboud			Daughter of Steven Aboud	10 3738 9873	Allander.Aboud@gmail.com
Chrysler		George	Abraham	George Abraham		Ground Investments	11 3738 9873	George.Abraham@gmail.com
Vertana		Chris	Abry	Chris Abry	Aboud	Head of Research	Investee Corp		12 3738 9873	Chris.Abry@gmail.com
Reagal		Menzie	Acharya	Menzie Acharya		Carline Venture	Ex Chairman of Aboud Investments	13 3738 9873	Menzie.Acharya@gmail.com
		Josh	Drickaya	Josh Drickaya	Drickaya	Senior Portfolio Manager	River Capital		14 3738 9873	Josh.Drickaya@gmail.com
		Robert	Adlee	Robert Adlee	Hargraves	Chief Executive Officer	Parks Investment	15 3738 9873	Robert.Adlee@gmail.com
		Kim	Dryer	Kim Dryer	Dryer	Office Manager/ EA to Robert Ell	Leda Capital	Met with Kim, interested in investing in new technology	16 3738 9873	Kim.Dryer@gmail.com
		Michelle	Hunter	Michelle Hunter	Caster	Personal Assistant to Steven Aboud	Merene Group	17 3738 9873	Michelle.Hunter@gmail.com

<colgroup><col><col><col span="2"><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Expected Outcomes (Highlighted cells indicate the cell that contains "Aboud")

FullName	Account Name	Job Title	Organisation	Notes	Phone	Email
Chris Armstrong		CEO at Capers Ltd	Capters	Principal of Aboud Family	04 3738 9873	Chris .Armstrong@gmail.com
Steven Aboud					6 3738 9873	Steven.Aboud@gmail.com
Russell Andrews		Chairman of Aboud Investments	9 3738 9873	Russell.Andrews@gmail.com
Allander Aboud				Daughter of Steven Aboud	10 3738 9873	Allander.Aboud@gmail.com
Chris Abry	Aboud	Head of Research	Investee Corp	12 3738 9873	Chris.Abry@gmail.com
Menzie Acharya			Carline Venture	Ex Chairman of Aboud Investments	13 3738 9873	Menzie.Acharya@gmail.com

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

@moodoggy

Let A:J of Sheet1 house the sample you posted, including the headers in the first row.

A:K of Sheet2 houses the processing...

Aboud	3
IDX	ACCOUNTS	FirstName	LastName	FullName	Account Name	Job Title	Organisation	Notes	Phone	Email
1	Aboud	Chris	Armstrong	Chris Armstrong	Redley	CEO at Capers Ltd	Capters		04 3738 9873	test@gmail.com
3	Frazer	Steven	Aboud	Steven Aboud					6 3738 9873	test@gmail.com
7	etc	Allander	Aboud	Allander Aboud					10 3738 9873	test@gmail.com

<tbody>
</tbody>

A1 houses an item of interest.

In B1 control+shift+enter, not just enter:

=SUM(IF(MMULT(ISNUMBER(SEARCH(A1,Sheet1!$A$2:$K$15))+0,TRANSPOSE(COLUMN(Sheet1!$A$2:$K$15)^0)),1))

In A3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$3:A3)>$B$1,"",SMALL(IF(MMULT(ISNUMBER(SEARCH($A$1,Sheet1!$A$2:$K$15))+0,TRANSPOSE(COLUMN(Sheet1!$A$2:$K$15)^0)),ROW(Sheet1!$A$2:$K$15)-ROW(Sheet1!$A$2)+1),ROWS($A$3:A3)))

In B3 just enter, copy across, and down:

=IF($A3="","",T(INDEX(Sheet1!$A$2:$K$15,$A3,MATCH(B$2,Sheet1!$A$1:$J$1,0))))

 

[moodoggy]

@moodoggy

Let A:J of Sheet1 house the sample you posted, including the headers in the first row.

A:K of Sheet2 houses the processing...

Aboud	3
IDX	ACCOUNTS	FirstName	LastName	FullName	Account Name	Job Title	Organisation	Notes	Phone	Email
1	Aboud	Chris	Armstrong	Chris Armstrong	Redley	CEO at Capers Ltd	Capters		04 3738 9873	test@gmail.com
3	Frazer	Steven	Aboud	Steven Aboud					6 3738 9873	test@gmail.com
7	etc	Allander	Aboud	Allander Aboud					10 3738 9873	test@gmail.com

<tbody>
</tbody>

A1 houses an item of interest.

In B1 control+shift+enter, not just enter:

=SUM(IF(MMULT(ISNUMBER(SEARCH(A1,Sheet1!$A$2:$K$15))+0,TRANSPOSE(COLUMN(Sheet1!$A$2:$K$15)^0)),1))

In A3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$3:A3)>$B$1,"",SMALL(IF(MMULT(ISNUMBER(SEARCH($A$1,Sheet1!$A$2:$K$15))+0,TRANSPOSE(COLUMN(Sheet1!$A$2:$K$15)^0)),ROW(Sheet1!$A$2:$K$15)-ROW(Sheet1!$A$2)+1),ROWS($A$3:A3)))

In B3 just enter, copy across, and down:

=IF($A3="","",T(INDEX(Sheet1!$A$2:$K$15,$A3,MATCH(B$2,Sheet1!$A$1:$J$1,0))))

Hi Aladin,

Thank you very much for your thorough and excellent response!
I have got it working.

There is only one issue and sorry for not being clear before.

Instead of have "Aboud" at the top as a reference, I need to be able to reference "Aboud" from Column A in the below outcomes example. This will then allow me to auto-fill down all of the Account names which has been multiplied by 5 (in an attempt to get at least 5 contacts related to that Account).

Is there a better way of doing this so that it finds every contact that contains "Aboud" and further Accounts rather than just 5?

Acc Ref	FullName	Job Title	Organisation	Notes	Phone	Email
Aboud	Chris Armstrong	CEO at Capers Ltd	Capters	Principal of Aboud Family	04 3738 9873	Chris .Armstrong@gmail.com
Aboud	Steven Aboud				6 3738 9873	Steven.Aboud@gmail.com
Aboud	Russell Andrews	Chairman of Aboud Investments		9 3738 9873	Russell.Andrews@gmail.com
Aboud	Allander Aboud			Daughter of Steven Aboud	10 3738 9873	Allander.Aboud@gmail.com
Aboud	Chris Abry	Head of Research	Investee Corp		12 3738 9873	Chris.Abry@gmail.com
Aboud	Menzie Acharya		Carline Venture	Ex Chairman of Aboud Investments	13 3738 9873	Menzie.Acharya@gmail.com
Andrews	…	…	…	…	…	…
Andrews	…	…	…	…	…	…
Andrews	…	…	…	…	…	…
Andrews	…	…	…	…	…	…
Andrews	…	…	…	…	…	…
Frazer	…	…	…	…	…	…
Frazer	…	…	…	…	…	…
Frazer	…	…	…	…	…	…
Frazer	…	…	…	…	…	…
Frazer	…	…	…	…	…	…

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>