returning data from a dropdown option

[Arturdj]

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 1	COMPANY 2	COMPANY 3	COMPANY 4
PRODUCT CODE	CODE	SIZE	PRICE	CODE	SIZE	PRICE	CODE	SIZE	PRICE	CODE	SIZE	PRICE
ABC123	1	M	7	1	L	7	1	S	7	1	XSM	7
DRE234	2	M	7	1	L	7	1	S	7	1	XSM	8
FRE356	3	M	7	1	L	7	1	S	7	1	XSM	9
FET533	4	M	7	1	L	7	1	S	7	1	XSM	10

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 CODE	COMPANY	CODE	SIZE	PRICE
ABC123	COMPANY 3	1	S	7

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

Thank you 

[#2]

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.

[#3]

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:

Thank you

[#4]

@Arturdj 

For such layout

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

where the text is in most left cell.