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:

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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>
 

DEHA11

New Member
Joined
Jun 25, 2018
Messages
15
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
 

DEHA11

New Member
Joined
Jun 25, 2018
Messages
15
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,225
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top