# INDEX MATCH with IF...or something else?

#### DEHA11

##### New Member
I'm attempting to return a value based on an INDEX, MATCH with an IF and just can't seem to get it nested properly so that it works...or maybe I should be using completely different logic.

The scenario:
Depending on how thick the metal is, we use different tools to cut or bend it. We are trying to put together a spreadsheet that will return the correct tool based on a formula because there are hundreds of thicknesses and hundreds of tools and these change regularly.

Our data is in two sheets in one workbook.

SHEET 1
 A B C 1 THICKNESS TYPE TOOL 2 .05 S ?? 3 .07 S ?? 4 .09 FL ??

<tbody>
</tbody>

SHEET 2
 A B C 1 VALUE1 TYPE TOOL 2 .03 S A 3 .06 FL B 4 .08 S C

<tbody>
</tbody>

I want excel to look at all of the data on sheet two (index A1:C4), make sure the types are the same (match 'sheet 1'!b2, 'sheet 2'!b2:b4,0) and then if the thickness is greater than the Value1 amount, return the tool name (match if 'sheet 1'!a2>sheet 2'a2:a4,c2:c4,0).

I can get each of these formulas to work on their own, but no matter how I nest them, I can't get them to work together.

Thanks!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### DanteAmor

##### Well-known Member
Try this array formula

<a2)*row(sheet2!\$c\$2:\$c\$4)))}

{=INDEX(Sheet2!\$C\$1:\$C\$4,MAX((Sheet2!\$B\$2:\$B\$4=B2)*(Sheet2!\$A\$2:\$A\$4 < A2)*ROW(Sheet2!\$C\$2:\$C\$4)))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</a2)*row(sheet2!\$c\$2:\$c\$4)))}>

Last edited:

#### DEHA11

##### New Member
Thank you for the array formula. I was able to enter it and the brackets are there, but it is not working as expected.

First, the "type" match (Sheet2!\$B\$2:\$B\$4=B2) isn't working, see illustration below.

Next, what I really need is the VALUE1 to be the next number that is less than the thickness. I believe that is what the array is asking for with "MAX", but I want to make sure that my assumption is correct. Otherwise the thickness of .0747 could return the result of Tool A or Tool B, but I really need it to be Tool B.

The actual formula I am using in cell C6 is: {=INDEX(Sheet2!\$D\$3:\$D\$1000,MAX((Sheet2!\$B\$3:\$B\$1000=\$D\$5)*(Sheet2!\$A\$3:\$A\$1000<\$C6)*ROW(Sheet2!\$D\$3:\$D\$1000)))}

Sheet1

 B C D 4 Material Thickness Tool/Type 5 S 6 16 Ga .0598 Result is Tool B should be A 7 14 Ga .0747 Result is Tool C should be B

<tbody>
</tbody>

Sheet2

 A B C D Value1 Type Length TOOL 3 .0310 S N/A Tool A 4 .062 S N/A Tool B 5 .125 FL 145 Tool C

<tbody>
</tbody>

Any idea why my results are not correct?

THANK YOU!!

Last edited:

#### DanteAmor

##### Well-known Member
The index should start in row 1

T

The actual formula I am using in cell C6 is: {=INDEX(Sheet2!\$D\$1:\$D\$1000,MAX((Sheet2!\$B\$3:\$B\$1000=\$D\$5)*(Sheet2!\$A\$3:\$A\$1000 < \$C6)*ROW(Sheet2!\$D\$3:\$D\$1000)))}