Array, array go away.... I'm Stuck. Please Help!

[DEHA11]

Sheet Name: 'TOOLS!'
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Opening[/TD]
[TD]Max Bend[/TD]
[TD]Tool[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.520[/TD]
[TD]150[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1.125[/TD]
[TD]105[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2.000[/TD]
[TD]95[/TD]
[TD]GHI[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3.000[/TD]
[TD]100[/TD]
[TD]JKL[/TD]
[/TR]
</tbody>[/TABLE]










Sheet Name: 'CHART!'
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Material[/TD]
[TD]Pref Opening[/TD]
[TD]Use Tool[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10GA[/TD]
[TD]1.125[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8GA[/TD]
[TD]2.000[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]







There are several other sheets and formulas in play, but the goal for this particular cell (Chart!C2) is to return the appropriate Tool based on multiple IF criteria.

The result for CHART!C2 should be

If the Max Bend of the Tool is greater than 90 AND
If the Max Bend of the Tool is less than 106 AND
If the Opening of the Tool is greater than or equal to the Preferred Opening
Return the Tool that has the closest Opening to the Preferred Opening

The formula I started with is shown below but it is returning a result of Tools!C1, “TOOL”, which is obviously not correct and does not ensure that I am getting the closest Opening match…

{=INDEX(Tools!$A$1:$C$5,IF(AND(Tools!$B$1:$B$5>90,Tools!$B$1:$B$5<106,Tools!$A$1:$A$5>$B2),MATCH($B2,Tools$A$1:$A$5,0),0))}

Any help is more than greatly appreciated since I have been stumped by this for over a week.

Thank you!

 

[Eric W]

Try:

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Material[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Pref Opening[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Use Tool[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]10GA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1.125[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]DEF[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]8GA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]GHI[/TD]

</tbody>

Chart​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=IFERROR(INDEX(Tools!$C$2:$C$10,MATCH(MIN(IF((Tools!$B$2:$B$10>90)*(Tools!$B$2:$B$10<106)*(Tools!$A$2:$A$10>=B2),Tools!$A$2:$A$10)),IF((Tools!$B$2:$B$10>90)*(Tools!$B$2:$B$10<106)*(Tools!$A$2:$A$10>=B2),Tools!$A$2:$A$10),0)),"No match")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[DEHA11]

Eric,

YOU ARE THE BEST!!!

I truly cannot thank you enough for your help with this. Having this formula finally work is almost enough to bring tears to my eyes!!

THANK YOU THANK YOU THANK YOU!!

-Dawndy

 

[Eric W]

Aw, shucks!

Just happy to help!

 

[DEHA11]

So, I'm looking at the formula you provided to better understand where I went wrong with my many attempts and I have one more question. Why is the bulk of the formula listed twice? What purpose does that serve?

Thanks
Deha

 

[Eric W]

This part:

IF((Tools!$B$2:$B$10>90)*(Tools!$B$2:$B$10<106)*(Tools!$A$2:$A$10>=B2),Tools!$A$2:$A$10)

is repeated twice, as you said. What it does is create an array of values that match your criteria. So looking at column Tools!B, we see if the value is > 90, and if the values is < 106, and if Tools!A is less than or equal to B2 from the other sheet. If one of those criteria is not met, the IF statement will return the FALSE value. So on the example above, row 2, that array turns out to be {FALSE;1.125;2;3;FALSE;FALSE;FALSE;FALSE;FALSE}. The non-FALSE values are the potential matches. So in this case we put the MIN function around it to get the smallest acceptable value or 1.125.

Now we know what the value is, but we need to get the name of the tool. So we create the array again, then use MATCH to find what position 1.125 is in that array, which turns out to be the second position. Then we use INDEX to find the second position of the C2:C10 range, which has the tool name.

Hope this helps!