(Option Compare) - then to copy data - help

[PeterOD]

Basically, i dont know how this compare fuctionality works, ive red about it and cant quite grasp it.

I have also seen it in code and cant grasp it to make it useful, so I am hoping to lean on the forum to prop me up. I am a novice in VBA and trying to learn as I go on, so go easy.

I have 6 sheets of calculations (all the same calculations across the 6 sheets, but using different input values which are imported from CSV)

To make sense of it all of the data, i have results sheet which pulls the values from the calculations sheets through using formula, image attached.

The problem I have is that I need to add more rows, and this messes up the formula, so I want to try and use a text comparison to find the correct result from the calculation sheet and bring that result into the correct cell on the report sheet. This would remove a load of code from my sheet and mean I just need to insert the text in my report to get the corresponding results to show up in the correct cells in that row.

I tried to explain this through a very small example sheet - attached. Open to suggestions if there is a better way.
If my explanation of the problem is crap, please make me aware.

Please ignore the incorrect totals, there is a problem elsewere (original sheet had 8 data sets) Real world application is used to count materials for construction as bommed out of cad design software.