lookup across mutliple pages return multiple results

[CreativeRova]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]AGENCY 1[/TD]
[TD]CLIENT[/TD]
[TD]CONTACT[/TD]
[TD]PHONE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]COMPANY 1[/TD]
[TD]Jane Doe[/TD]
[TD]11111111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Joe Doe[/TD]
[TD]22222222[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Simon Blog[/TD]
[TD]3333333[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sarah Smith[/TD]
[TD]444444[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]COMPANY 2[/TD]
[TD]Jane Doe[/TD]
[TD]11111111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sam Smith[/TD]
[TD]5555555[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sarah Smith[/TD]
[TD]444444[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Steve Jones[/TD]
[TD]66666666[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi Excel people,

I have an workbook like the above with many tabs/pages that look like the above. Each tab has a different agency name "agency 1" "agency 2" etc. then each one has different clients "company 1" company 2" etc.

I am after a search page at the front that looks like the table below.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]COMPANY 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AGENCY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]????[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CONTACTS[/TD]
[TD]PHONE[/TD]
[/TR]
[TR]
[TD]????[/TD]
[TD]????[/TD]
[/TR]
[TR]
[TD]????[/TD]
[TD]????[/TD]
[/TR]
[TR]
[TD]????[/TD]
[TD]????[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to type in A2 a company name and then auto fill agency, contacts and phone from the correct page. the contacts may have 1 person it may have 10. So it needs to show all relevant names until column B reaches a new company name.

Please Help.

 

[James006]

Hi,

Just take a look at XLOR 's excellent solution ...

https://excelxor.com/2014/11/15/vlookup-across-several-worksheets-2-multiple-search-criteria-2/

HTH

 

[CreativeRova]

I looked at that. It is the most confusing thing i have ever seen.