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

Part Number			Part number	Part Number	Part Number
1	Custom Fittings		Custom Fittings	Carbon Steel	Stainless Steel
2	Carbon Steel		1	Try again!	Try Again!
3	Stainless Steel

<tbody>
</tbody>

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]

	A	B	C	D
1	AL CxE	321 Single Braid	Tank Truck	PGP
2	SS A	Monel Single	Steam	AAP

<tbody>
</tbody>

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