VLOOKUP, HLOOKUP or MATCH?

[Legacy 90606]

The table I have below is a list of consultant details.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Consultancy Name[/TD]
[TD]Consultant[/TD]
[TD]Email Address[/TD]
[TD]Phone Number[/TD]
[/TR]
[TR]
[TD]Consultancy 1[/TD]
[TD]Mr Smith[/TD]
[TD]smith@cons.com[/TD]
[TD]020 155 6500[/TD]
[/TR]
[TR]
[TD]Consultancy 1[/TD]
[TD]Ms Wright[/TD]
[TD]wright@cons.com[/TD]
[TD]020 155 6501[/TD]
[/TR]
[TR]
[TD]Consultancy 2[/TD]
[TD]Mr Dunbar[/TD]
[TD]dunbar@cons.com[/TD]
[TD]020 155 6502[/TD]
[/TR]
[TR]
[TD]Consultancy 1[/TD]
[TD]Mr Peston[/TD]
[TD]peston@cons.com[/TD]
[TD]020 155 6503[/TD]
[/TR]
[TR]
[TD]Consultancy 2[/TD]
[TD]Mrs Andrews[/TD]
[TD]andrews@cons.com[/TD]
[TD]020 155 6504[/TD]
[/TR]
[TR]
[TD]Consultancy 3[/TD]
[TD]Mr Cooper[/TD]
[TD]cooper@cons.com[/TD]
[TD]020 155 6505[/TD]
[/TR]
[TR]
[TD]Consultancy 1[/TD]
[TD]Ms Lewis[/TD]
[TD]lewis@cons.com[/TD]
[TD]020 155 6506[/TD]
[/TR]
</tbody>[/TABLE]

A dropdown will then let you select Consultancy 1 / Consultancy 2/ Consultancy 3 which should then display the following results in a separate table.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Consultancy 1[/TD]
[TD]Consultancy 2[/TD]
[TD]Consultancy 3[/TD]
[/TR]
[TR]
[TD]Mr Smith[/TD]
[TD]Mr Dunbar[/TD]
[TD]Mr Cooper[/TD]
[/TR]
[TR]
[TD]Ms Wright[/TD]
[TD]Mrs Andrews[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr Peston[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ms Lewis[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

You would have to use a macro to get that output or some fairly complex Array formulas.
However, have you considered using a Table Slicer?

Also, users with O365 that participate in the advanced Office Insiders program have access to a new calculation Engine and Array formulas that could get your results fairly easily.

 

[Legacy 90606]

If the dropdown feature was removed would there be an array formula that could be used for each 3 consultancies.

 

[AhoyNC]

If you have Excel ver. 2010 or later try:
Copy formula down and across as needed.

Excel Workbook
	A	B	C	D
1	Consultancy Name	Consultant	Email Address	Phone Number
2	Consultancy 1	Mr Smith	smith@cons.com	020 155 6500
3	Consultancy 1	Ms Wright	wright@cons.com	020 155 6501
4	Consultancy 2	Mr Dunbar	dunbar@cons.com	020 155 6502
5	Consultancy 1	Mr Peston	peston@cons.com	020 155 6503
6	Consultancy 2	Mrs Andrews	andrews@cons.com	020 155 6504
7	Consultancy 3	Mr Cooper	cooper@cons.com	020 155 6505
8	Consultancy 1	Ms Lewis	lewis@cons.com	020 155 6506
9
10
11	Consultancy 1	Consultancy 2	Consultancy 3
12	Mr Smith	Mr Dunbar	Mr Cooper
13	Ms Wright	Mrs Andrews
14	Mr Peston
15	Ms Lewis
Sheet

 

[SpillerBD]

If the dropdown feature was removed would there be an array formula that could be used for each 3 consultancies.

Drop downs still exist and have not changed outside of some additions. However, the table format you are asking for has not existed with a dropdown list. Dropdowns have been primarily under Auto-Filters or Data Validation options.
So yes you could use a cell with Data Validation to make your choice and then, using a change event, create the table as you have demonstrated.
AHoy's solution is valid, but I don't like having to encompass more space than declared or the table not expanding with records in a changing environment. Very good for a one-off/static instance, but even a pivot table would be more flexible.

Pivot Tabl:, Consultancy Name in Filter Area, Consultants list in Rows. The first row/Filter Row may be objectionable because it will be 2 columns wide. Header and selected value.