returning data from a dropdown option

%3CLINGO-SUB%20id%3D%22lingo-sub-2415756%22%20slang%3D%22en-US%22%3Ereturning%20data%20from%20a%20dropdown%20option%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2415756%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20some%20help%20with%20a%20spreadsheet%20that%20i%20am%20creating.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20setup%20a%20dropdown%20option%20to%20select%20the%20relevant%20company%20and%20return%20the%20necessary%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20the%20data%20looks%20like%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22878px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22103px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22198px%22%3ECOMPANY%201%3C%2FTD%3E%3CTD%20width%3D%22192px%22%3ECOMPANY%202%3C%2FTD%3E%3CTD%20width%3D%22192px%22%3ECOMPANY%203%3C%2FTD%3E%3CTD%20width%3D%22192px%22%3ECOMPANY%204%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22103px%22%3EPRODUCT%20CODE%3C%2FTD%3E%3CTD%20width%3D%2271px%22%3ECODE%3C%2FTD%3E%3CTD%20width%3D%2255px%22%3ESIZE%3C%2FTD%3E%3CTD%20width%3D%2272px%22%3EPRICE%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3ECODE%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3ESIZE%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3EPRICE%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3ECODE%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3ESIZE%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3EPRICE%3C%2FTD%3E%3CTD%20width%3D%2268px%22%3ECODE%3C%2FTD%3E%3CTD%20width%3D%2256px%22%3ESIZE%3C%2FTD%3E%3CTD%20width%3D%2268px%22%3EPRICE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22103px%22%3EABC123%3C%2FTD%3E%3CTD%20width%3D%2271px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2255px%22%3EM%3C%2FTD%3E%3CTD%20width%3D%2272px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3EL%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3ES%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2268px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2256px%22%3EXSM%3C%2FTD%3E%3CTD%20width%3D%2268px%22%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22103px%22%3EDRE234%3C%2FTD%3E%3CTD%20width%3D%2271px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2255px%22%3EM%3C%2FTD%3E%3CTD%20width%3D%2272px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3EL%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3ES%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2268px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2256px%22%3EXSM%3C%2FTD%3E%3CTD%20width%3D%2268px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22103px%22%3EFRE356%3C%2FTD%3E%3CTD%20width%3D%2271px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2255px%22%3EM%3C%2FTD%3E%3CTD%20width%3D%2272px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3EL%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3ES%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2268px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2256px%22%3EXSM%3C%2FTD%3E%3CTD%20width%3D%2268px%22%3E9%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22103px%22%3EFET533%3C%2FTD%3E%3CTD%20width%3D%2271px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2255px%22%3EM%3C%2FTD%3E%3CTD%20width%3D%2272px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3EL%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2254px%22%3ES%3C%2FTD%3E%3CTD%20width%3D%2269px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2268px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2256px%22%3EXSM%3C%2FTD%3E%3CTD%20width%3D%2268px%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20aim%20would%20be%20for%20the%20individual%20to%20enter%20the%20product%20code%20then%20select%20the%20company%20to%20return%20the%20data%20from%20the%20relevant%20sections%20i.e.%20if%20ABC123%20was%20entered%20and%20company%203%20selected%20then%20the%20CODE%2C%20SIZE%20and%20PRICE%20would%20be%20gathered%20from%20that%20company%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22377%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22102px%22%3EPRODUCT%20CODE%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ECOMPANY%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3ECODE%3C%2FTD%3E%3CTD%20width%3D%2262px%22%3ESIZE%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3EPRICE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22102px%22%3EABC123%3C%2FTD%3E%3CTD%20width%3D%2296px%22%3ECOMPANY%203%3C%2FTD%3E%3CTD%20width%3D%2253px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2262px%22%3ES%3C%2FTD%3E%3CTD%20width%3D%2263px%22%3E7%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20quite%20sure%20how%20to%20go%20about%20it.%20Any%20advice%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2415756%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2415858%22%20slang%3D%22en-US%22%3ERe%3A%20returning%20data%20from%20a%20dropdown%20option%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2415858%22%20slang%3D%22en-US%22%3EIt's%20a%20bit%20difficult%20to%20say%2C%20but%20it%20looks%20to%20me%20as%20if%20your%20data%20is%20not%20organized%20in%20such%20a%20way%20as%20to%20make%20it%20possible%20to%20do%20what%20you%20want%20to%20do.%20%22Company%203%22%20is%20just%20a%20heading%20over%20another%20heading%20(sub-heading%3F)%20that%20is%20%22Price%22%3CBR%20%2F%3EPerhaps%20you%20could%20post%20an%20actual%20copy%20of%20an%20actual%20spreadsheet%2C%20so%20long%20as%20there's%20no%20proprietary%20information%20in%20it.%20Basically%2C%20though%2C%20it%20looks%20very%20much%20like%20the%20data%20itself%20needs%20to%20be%20organized%20differently%20as%20a%20%22flat%20file%22%20database....then%20what%20you're%20seeking%20to%20do%20will%20be%20relatively%20easy.%20That%20means%2C%20though%2C%20that%20each%20combination%20of%20Company%20and%20Product%20deserves%20its%20own%20row%20of%20data.%3C%2FLINGO-BODY%3E
New Contributor

Hi All,

 

I am looking for some help with a spreadsheet that i am creating.

 

I want to setup a dropdown option to select the relevant company and return the necessary data.

 

This is what the data looks like: 

 COMPANY 1COMPANY 2COMPANY 3COMPANY 4
PRODUCT CODECODESIZEPRICECODESIZEPRICECODESIZEPRICECODESIZEPRICE
ABC1231M71L71S71XSM7
DRE2342M71L71S71XSM8
FRE3563M71L71S71XSM9
FET5334M71L71S71XSM10

 

The aim would be for the individual to enter the product code then select the company to return the data from the relevant sections i.e. if ABC123 was entered and company 3 selected then the CODE, SIZE and PRICE would be gathered from that company

 

PRODUCT CODECOMPANYCODESIZEPRICE
ABC123COMPANY 31S7

 

Not quite sure how to go about it. Any advice would be appreciated.

 

Thank you 

 

3 Replies
It's a bit difficult to say, but it looks to me as if your data is not organized in such a way as to make it possible to do what you want to do. "Company 3" is just a heading over another heading (sub-heading?) that is "Price"
Perhaps you could post an actual copy of an actual spreadsheet, so long as there's no proprietary information in it. Basically, though, it looks very much like the data itself needs to be organized differently as a "flat file" database....then what you're seeking to do will be relatively easy. That means, though, that each combination of Company and Product deserves its own row of data.

Hi @mathetes 

 

I thought that might be the case; designing the data to be flat I just didn't know if it was possible with the current layout.

With that being said do you have any advice on how to covert the data to a 'flat file' with the current format?

 

Also the data should look like this:

Arturdj_0-1622809942795.png

 

Thank you

@Arturdj 

For such layout

image.png

it could be in D12

=INDEX($C$4:$N$7,
   MATCH($B12, $B$4:$B$7,0),
   MATCH($C12,$C$2:$N$2,0)+COLUMN()-COLUMN($D$11)
)

and drag it to the right and down.

If in such style I'd recommend not not to merge cells but use Center Across Selection

image.png

where the text is in most left cell.