VLOOKUP, HLOOKUP or MATCH?

[Legacy 90606]

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

Consultancy Name	Consultant	Email Address	Phone Number
Consultancy 1	Mr Smith	smith@cons.com	020 155 6500
Consultancy 1	Ms Wright	wright@cons.com	020 155 6501
Consultancy 2	Mr Dunbar	dunbar@cons.com	020 155 6502
Consultancy 1	Mr Peston	peston@cons.com	020 155 6503
Consultancy 2	Mrs Andrews	andrews@cons.com	020 155 6504
Consultancy 3	Mr Cooper	cooper@cons.com	020 155 6505
Consultancy 1	Ms Lewis	lewis@cons.com	020 155 6506

<tbody>
</tbody>

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

Consultancy 1	Consultancy 2	Consultancy 3
Mr Smith	Mr Dunbar	Mr Cooper
Ms Wright	Mrs Andrews
Mr Peston
Ms Lewis

<tbody>
</tbody>

 

[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.