Data Extraction using sector wise?

[SONJOE JOSEPH]

I have some data in Sheet 1. Using this data i need to extract the data according to sectorwise. My idea is if i create a drop down in K1 (yellow) so once i click on the sector say "PHARMA" from the dropdown list in K1 all the company names which are related to pharma with their respective PE, EY, ROCE, ROE has to come in the new heading. Please help me out how this can be done.

Working file attached.

Regards
Sonjoe

 

[AlanSidman]

With Power Query, I have created a parameter query which you can see in the attached. Follow the instruction in the worksheet.

 

[SONJOE JOSEPH]

Can the problem be solved using the vlookup or match ....Do let me know. I have some calculations to be performed. I

 

[shrivallabha]

If you have Office 365 and FILTER function then following approach can be used:

To find out companies matching criteria:
=FILTER(B3:B10,C3:C10=K1)

And then a typical formula to produce rest of the data:
=VLOOKUP($J3#,$B$2:$G$10,MATCH(K$2,$B$2:$G$2,0))

Please see attached file for demonstration.

 

[Peter Bartholomew]

If you have Office 365 and FILTER function then following approach can be used:

To find out companies matching criteria:
=FILTER(B3:B10,C3:C10=K1)

And then a typical formula to produce rest of the data:
=VLOOKUP($J3#,$B$2:$G$10,MATCH(K$2,$B$2:$G$2,0))

Please see attached file for demonstration.

I had gone for a second filter rather than the lookups. Building on your idea of a lookup, I returned the sequence number and then used it to return data, with columns in any order, as a single spilt range. I wonder how many users would even recognise the formula as being Excel?

 

[shrivallabha]

I had gone for a second filter rather than the lookups. Building on your idea of a lookup, I returned the sequence number and then used it to return data, with columns in any order, as a single spilt range. I wonder how many users would even recognise the formula as being Excel?
View attachment 75316

Since OP had mentioned VLOOKUP and MATCH, I demonstrated with it limiting the newer function to its core requirements. Unfortunately, we have not received update with LET so I get the typical _xlfn.LET on my computer with your file. New functions added by Microsoft are powerful and appear more like SQL / Code like sometimes to me. I would not be surprised someday if we get a SELECT function which probably will look like below
=SELECT(field,with,table,DB)
and be able to fetch values from DB specified by user. Sorry for digressing from the subject.

 

[Peter Bartholomew]

Sorry about that; I didn't realise that LET has not completed its rollout. It is strange that, while LET provides no new functionality, it is such a useful aid to developing formulas which are, to an extent, self-documenting. For a while the data-management tools of Tables and PowerQuery outstripped the world of spreadsheet formulas but I think dynamic arrays have redressed the balance and FILTER even allows array techniques to compete when applied to data manipulation problems, at least to the point of 10,000 or so records.

I had rather assumed that PowerQuery would remain the preferred route to accessing external data but the ability to read from, and write to, database tables directly from the worksheet would assist collaboration.