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!
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]My Company
[/TD]
[TD]Comp 1
[/TD]
[TD]Comp 2
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]AA
[/TD]
[TD]BB
[/TD]
[TD]CC
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]AAA
[/TD]
[TD]BBB
[/TD]
[TD]CCC
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]AAAA
[/TD]
[TD]BBBB
[/TD]
[TD]CCCC
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Type Competitor Product Below
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]CC
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]My Company - Result Below
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]AA
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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