INDEX MATCH with IF...or something else?

[DEHA11]

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
[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]THICKNESS[/TD]
[TD="align: center"]TYPE[/TD]
[TD="align: center"]TOOL[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"].05[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]??[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"].07[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]??[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"].09[/TD]
[TD="align: center"]FL[/TD]
[TD="align: center"]??[/TD]
[/TR]
</tbody>[/TABLE]

SHEET 2
[TABLE="class: grid, width: 200, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]VALUE1[/TD]
[TD="align: center"]TYPE[/TD]
[TD="align: center"]TOOL[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"].03[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"].06[/TD]
[TD="align: center"]FL[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"].08[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]C[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[DanteAmor]

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

 

[DEHA11]

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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Material[/TD]
[TD="align: center"]Thickness[/TD]
[TD="align: center"]Tool/Type[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]S[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]16 Ga[/TD]
[TD="align: center"].0598[/TD]
[TD="align: center"]Result is Tool B should be A[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]14 Ga[/TD]
[TD="align: center"].0747[/TD]
[TD="align: center"]Result is Tool C should be B[/TD]
[/TR]
</tbody>[/TABLE]









Sheet2

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Value1[/TD]
[TD]Type[/TD]
[TD]Length[/TD]
[TD]TOOL[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD].0310[/TD]
[TD]S[/TD]
[TD]N/A[/TD]
[TD]Tool A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD].062[/TD]
[TD]S[/TD]
[TD]N/A[/TD]
[TD]Tool B[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD].125[/TD]
[TD]FL[/TD]
[TD]145[/TD]
[TD]Tool C[/TD]
[/TR]
</tbody>[/TABLE]









Any idea why my results are not correct?

THANK YOU!!

 

[DanteAmor]

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