Index Match - Multiple Columns

[bloodybrit90]

I am having trouble with the =index(match function when I have multiple columns. What I am trying do is determine what product my company offers (Column A) based on a know competitor product (Column B and Column C). Below is an example of what I am attempting to do. When I use the formula below I receive #N/A

{=INDEX(A2:A5,MATCH(A9,B2:C5,0))}

Any help would be great!

	A	B	C
1	My Company	Comp 1	Comp 2
2	A	B
3	AA	BB	CC
4	AAA	BBB	CCC
5	AAAA	BBBB	CCCC
6
7
8	Type Competitor Product Below
9	CC
10
11	My Company - Result Below
12	AA

<tbody>
</tbody>

 

[AhoyNC]

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

Excel Workbook
	A	B	C
1	My Company	Comp 1	Comp 2
2	A	B
3	AA	BB	CC
4	AAA	BBB	CCC
5	AAAA	BBBB	CCCC
6
7
8	Type Competitor Product Below
9	CC
10
11	My Company - Result Below
12	AA
Sheet

 

[FDibbins]

Will there only be 1 match, or could there be many?

 

[Guru008]

Hi,

Try this

=IFERROR(INDEX(K31:K35,IFERROR(MATCH(K39,L31:L35,0),MATCH(K39,M31:M35,0))),"")

 

[bloodybrit90]

Will there only be 1 match, or could there be many?

Yes, in some circumstances there could be 2-3 matches. How would you handle this?

 

[AhoyNC]

Maybe something like this:
Array formula that needs to be entered with CTRL-SHIFT-ENTER.

Copy formula in A12 of the example below across as needed.

Excel Workbook
	A	B	C
1	My Company	Comp 1	Comp 2
2	A	B
3	AA	BB	CC
4	AAA	BBB	CCC
5	AAAA	BBBB	CCCC
6	AASD	B1	CC
7
8	Type Competitor Product Below
9	CC
10
11	My Company - Result Below
12	AA	AASD
Sheet

 

[bloodybrit90]

AhoyNC that worked perfectly. Thank you all for the help!!

I will have to spend some time reviewing the Search, row, column function to better familiarize myself.

Thanks again