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

DEHA11

New Member
Joined
Jun 25, 2018
Messages
15
Sheet Name: 'TOOLS!'
ABC
1OpeningMax BendTool
20.520150ABC
31.125105DEF
42.00095GHI
53.000100JKL

<tbody>
</tbody>










Sheet Name: 'CHART!'
ABC
1MaterialPref OpeningUse Tool
210GA1.125???
38GA2.000

<tbody>
</tbody>







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!
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Try:

ABC
1MaterialPref OpeningUse Tool
210GA1.125DEF
38GA2GHI

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Chart

Array Formulas
CellFormula
C2{=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")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top