• 466K Members
• 9,412 Online
• 563K Conversations

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

# 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies