Dropdown Menu Help!

I have pulled together a lot of product information for various KVM products on the market. What I am trying to do is pull product information from the 'Data' tab, and have it displayed on the Product Comparison tab. The plan is to be able to compare up to four products on the product Comparison tab at any given time.

I want to use a dropdown menu that will show each Part Number (on the Product Comparison tab), and when you click on that P/N #, it populates all of that parts data from Data tab. Example, if you click on F1DA104Z under Product 1, it will populates the Product Comparison tab with cells B2 through B65 from the Data tab. I need to do this for each P/N # on the Data tab (in total I have 338 I need to do this to).

What is the best way to do this, and is it possible to have the dropdown of Part Numbers be categorized so they are easier to understand for others besides myself. An example of the dropdown structure I'd like to use is shown below. My plan would be that no data is shown if you click on the Company Name or Category from the dropdown menu *they are just placeholders*, but instead...data is only populated when the Part Number is selected.

Company Name
KVM Category
Part Number 1
Part Number 2
etc. etc.

Please let me know if there is any easier way to achieve this. I just need a push in the right direction, and I should be able to replicate it across all of the different Part Numbers.

Thanks in advance!

Data Example.xlsx

I think I understand your formula, but is there anyway to paste it into another location besides c6 on the DATA tab? Pasting it into c6 changes the data in that cell. Also, the formula is only covering C4 to I4 instead of extending to B4. Is it possible for me to change it to extend to B4 without messing up the formula you created? I assume so, but this is new to me.

Also, how can I link this formula to a dropdown menu on the Product Comparison tab? I tried using data validation but I am doing something wrong, as I am unable to link the formula to a Part Number driven dropwdown menu on the Product Comparison tab.

Thanks in advance!

Yes, I am trying to have it populate on the PRODUCT COMPARISON tab, I missunderstood your directions.

However, I am trying to do all of this via dropdowns, instead of manually pasting the formula over. The issue I am having is how to link the cell data from the DATA tab (via a Part Number parent relationship) to a dropdown in the PRODUCT COMPARISON tab. I've looked online at creating the dropdown menus via data validation, but it is not pulling through the applicable cell data. That is really where I am struggling; how to link the cell data to the dropdown menu. I am creating this for my sales team, so it needs to be as easy as possible for them. Pasting over a formula won't be easy enough for them. ;(

Thanks! I was able to figure it out by moving some data around to accomodate this need with the formulas supplied. Thanks again for your help!