Highlighted
Contributor

# Tough formula for index/match/vlookup/small and array.....I can't solve!

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.

I wonder if anyone has a unique solution to this?

Thanks,

Greg

 DATA HOW TO POPULATE CHART? How it should be populated QC File how many High Low Dil High Low Dil High Run01 1 Run01 ? ? ? Run01 1 2 Low Run01 2 Run01 ? ? ? Run01 4 3 Low Run01 3 Run02 ? ? ? Run02 5 6 10 High Run01 4 Run02 ? ? ? Run02 8 7 11 High Run02 5 Low Run02 6 Low Run02 7 High Run02 8 Dil Run02 10 Dil Run02 11

1 Reply
Highlighted

# Re: Tough formula for index/match/vlookup/small and array.....I can't solve!

Hi @gms4b

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,

=MIN(IF(\$A\$3:\$A\$12=F\$2,IF(\$B\$3:\$B\$12=\$E3,\$C\$3:\$C\$12)))

=MAX((\$A\$3:\$A\$12=F\$2)*(\$B\$3:\$B\$12=\$E4)*\$C\$3:\$C\$12)

Please use (Ctrl + Shift + Enter) as they are array formula.

I have also attached a sample file for your reference. Please let me know if it works for you.

Thanks

Tauqeer