I am usually pretty good with formulas but can't figure this one out! I have a bunch of data in the chart on the left that needs to be reorganized into the format in the middle. The ultimate output should look like the one on the right.
The problem is that you can't really use Vlookup or index/match because there is no unique value (i.e. there are two pairs of Run1 and QC Low....that have 2 different values. In other places I have used an array, but that doesn't really work because the column sizes are different. I've also used an array with SMALL to sort the data....this works well, but that would move values from Dil and Run2 (i.e. 10 and 11) up two cells next to Run1, which isn't right.
Your requirement is quite achievable, since you look for two different values for each category therefore you need to use two different formulas (one for minimum value & other for maximum value). You can try below formulas,