INDEX MATCH with IF...or something else?

DEHA11

New Member
Joined
Jun 25, 2018
Messages
15
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
ABC
1THICKNESSTYPETOOL
2.05S??
3.07S??
4.09FL??

<tbody>
</tbody>

SHEET 2
ABC
1VALUE1TYPETOOL
2.03SA
3.06FLB
4.08SC

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

Some videos you may like

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
Joined
Dec 3, 2018
Messages
10,271
Office Version
2007
Platform
Windows
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
Joined
Jun 25, 2018
Messages
15
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

BCD
4MaterialThicknessTool/Type
5S
616 Ga.0598Result is Tool B should be A
714 Ga.0747Result is Tool C should be B

<tbody>
</tbody>









Sheet2

ABCD
Value1TypeLengthTOOL
3.0310SN/ATool A
4.062SN/ATool B
5.125FL145Tool C

<tbody>
</tbody>









Any idea why my results are not correct?

THANK YOU!!
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,271
Office Version
2007
Platform
Windows
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)))}
 

Watch MrExcel Video

Forum statistics

Threads
1,090,453
Messages
5,414,633
Members
403,538
Latest member
indiemusicboy

This Week's Hot Topics

Top