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

#### DEHA11

##### New Member
Sheet Name: 'TOOLS!'
 A B C 1 Opening Max Bend Tool 2 0.520 150 ABC 3 1.125 105 DEF 4 2.000 95 GHI 5 3.000 100 JKL

<tbody>
</tbody>

Sheet Name: 'CHART!'
 A B C 1 Material Pref Opening Use Tool 2 10GA 1.125 ??? 3 8GA 2.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

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
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
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

##### MrExcel MVP
Aw, shucks!

Just happy to help!

#### DEHA11

##### New Member
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
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!

Replies
3
Views
324
Replies
11
Views
581
Replies
4
Views
248
Replies
3
Views
235
Replies
3
Views
269

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