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
<tbody>
</tbody>
SHEET 2
<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!
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!