iferror Index Match - Error

[Enzo_Matrix]

=IFERROR(INDEX($A$2:$A$60,MATCH(D$1,$A$1,0),MATCH(D$2,$B2:$B60,0)),"Try Again!")

The idea is that if I choose a different cell in 'C' it will give the response from 'B'. However it only functions for the first option chosen in the matrix B1,C1. Everything else produces an error[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD][/TD]
[TD][/TD]
[TD]Part number[/TD]
[TD]Part Number[/TD]
[TD]Part Number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Custom Fittings[/TD]
[TD][/TD]
[TD]Custom Fittings[/TD]
[TD]Carbon Steel[/TD]
[TD]Stainless Steel[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Carbon Steel[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Try again![/TD]
[TD]Try Again![/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Stainless Steel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Am I missing something in the code?

 

[AhoyNC]

Is this what you want?

Excel Workbook
	A	B	C	D	E	F
1	Part Number			Part number	Part Number	Part Number
2	1	Custom Fittings		Custom Fittings	Carbon Steel	Stainless Steel
3	2	Carbon Steel		1	2	3
4	3	Stainless Steel
Sheet

 

[Enzo_Matrix]

I need D3 to change it's number depending on the text in D2.

This table has 60 lines in it currently and has the potential to grow and I do not think writing a 60 nested IF statement is the best way to go about it.

 

[AhoyNC]

Not sure I understand. The formula I gave above will return a part number based on the text in D2. Don't see the need for 60 nested IF statements.
Just need to copy formula across columns as needed.

Excel Workbook
	A	B	C	D	E	F
1	Part Number			Part number	Part Number	Part Number
2	1	Custom Fittings		Carbon Steel	Stainless Steel	Custom Fittings
3	2	Carbon Steel		2	3	1
4	3	Stainless Steel
Sheet

 

[Enzo_Matrix]

Perfect, thanks

 

[Enzo_Matrix]

So I have come across another issue with this and I'm hoping you can help me out.

This works for 2 columns, but can't get it to work for 4. This might potentially increase in scope as time goes on.

Is it possible to add to the below code to accommodate a 3rd and 4th match or is there a VBA option that can be used in it's place?

=IFERROR(INDEX(Data!$A$3:$A$31,IFERROR(MATCH($I6,Data!$I$3:$I$31,0),MATCH($I6,Data!$L$3:$L$18,0))),"Try Again!")

 

[AhoyNC]

How about posting a sample and the result you are looking for.

 

[Enzo_Matrix]

[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]AL CxE[/TD]
[TD="align: center"]321 Single Braid[/TD]
[TD="align: center"]Tank Truck[/TD]
[TD="align: center"]PGP[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]SS A[/TD]
[TD="align: center"]Monel Single[/TD]
[TD="align: center"]Steam[/TD]
[TD="align: center"]AAP[/TD]
[/TR]
</tbody>[/TABLE]





I have lists with Data validation in Columns A:D. If you choose 'Monel Single' it will give '2'. If I choose 'AL CxE' it will give 1. However If I chose anything from column C or D it returns the error message 'Try Again!' as it should.

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1	1	AL CxE	321 Single Braid	Tank Truck	PGP
2	2	SS A	Monel Single	Steam	AAP
3
4
5	Find	Steam
6		2
Sheet

 

[AhoyNC]

Try this instead of what I have in post #9 above.
It's still an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1	1	AL CxE	321 Single Braid	Tank Truck	PGP
2	2	SS A	Monel Single	Steam	AAP
3
4
5	Find	Steam
6		2
Sheet