Drop down list fills out table for data entry guidelines

[kylewestplays]

Hello!

I hope I have come to the right place. I have been searching for the past several days for a solution to my conundrum and I haven't been able to find anything.

The company that I work for is...significantly behind the times in regards to data entry and computer use. I have recently been redirected to the quality department, where quality tests are done on the products that we produce and the tests are measured against certain standards set forth by the higher ups in the quality department. That said, all of the calculations and data entry is done by hand using paper and pencil, and I have taken it upon myself to come up with an easier, efficient, and effective means of documenting these tests.

I have designed an Excel worksheet for the data entry. The really basic stuff, like many of the calculations that we were once using calculators for, I was able to modernize with no problem. Truth be told, the worksheet I have created is already vastly better than the original method, but I want to pull out all the stops before I go to the heads of the quality department with my redesign.

Here is a picture of the worksheet.
https://imgur.com/a/kzOHs3U

What I would like to accomplish next is having the Limits table change to reflect the product being tested when the product is selected from the Code #: drop down list.

So, for example, Product A is selected from the Code #: drop down list, all the cells from
A10 to Z14 are changed to reflect the quality standards for Product A. Rinse and repeat for Product B through D.

I've done some research, and I've had some minimal success. there appear to be different methods for achieving what I want to get done. One method involved Index and VLookup, another method involving Power Queries.

My knowledge of Excel, Excel functions, and Excel formulas are very basic, but I have a mind for programming and I'm a quick learner. I'm not here to ask someone to do the work for me, but if someone could provide an example of how I can achieve what I want done that I can look at and figure out what's going on, that would help out tremendously.

If it helps, I've already created a table containing the information for each product on a second sheet, but where I'm running into a snag is how to translate that information onto the main sheet when the product code is selected from the drop down menu.

Thanks a bunch in advance for taking to time to understand my problem!

 

[DanteAmor]

I do not know how your data is on the other sheet, therefore I do not know how to search and what data to obtain.
But let's start with this:

=SUMPRODUCT((Sheet2!$B$1:$D$1=$A11)*(Sheet2!$A$2:$A$4=$C$3)*(Sheet2!$B$2:$D$4))
=INDEX(Sheet2!$A$1:$E$4,MATCH($C$3,Sheet2!$A$1:$A$4,0),MATCH($A12,Sheet2!$A$1:$E$1,0))

The above formulas perform, in a different way, a search and obtain the same result.

I attach the file so you can see how they work. Check the color details in the formula.

https://www.dropbox.com/s/3vlrq10x45452fk/Drop down fills.xlsx?dl=0