Help with INDEX and MATCH or it it something else

[Sean15]

Hi:

INDEX and MATCH is not returning required value in column D. Could you help me please?

Excel 2010
	A	B	C	D	E
1	Sheet1
2	Name	Type	No sold	TL Sales	Expected value
3	Alpine	Pears	12	15,000.00	15,000.00
4	Alpine	Apples	10	15,000.00	-
5	Bata	Apples	24	12,000.00	12,000.00
6	Bata	Oranges	52	12,000.00	-
7	Catnip	Pears	45	5,000.00	5,000.00
8	Catnip	Onions	62	5,000.00	-
9
10
11
12	Sheet2
13	Names	Sales
14	Alpine	15,000.00
15	Bata	12,000.00
16	Catnip	5,000.00
Sheet1 (2)

Cell Formulas
Range	Formula
D3	=INDEX($B$14:$B$16,MATCH(A3,$A$14:$A$16,0))
D4	=INDEX($B$14:$B$16,MATCH(A4,$A$14:$A$16,0))
D5	=INDEX($B$14:$B$16,MATCH(A5,$A$14:$A$16,0))
D6	=INDEX($B$14:$B$16,MATCH(A6,$A$14:$A$16,0))
D7	=INDEX($B$14:$B$16,MATCH(A7,$A$14:$A$16,0))
D8	=INDEX($B$14:$B$16,MATCH(A8,$A$14:$A$16,0))

Thanks

Regards,

Sean

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D
1	Name	Type	No sold	TL Sales
2	Alpine	Pears	12	15,000.00
3	Alpine	Apples	10
4	Bata	Apples	24	12,000.00
5	Bata	Oranges	52
6	Catnip	Pears	45	5,000.00
7	Catnip	Onions	62
8
9
10
11	Sheet2
12	Names	Type	Sales
13	Alpine	Pears	15,000.00
14	Bata	Apples	12,000.00
15	Catnip	Pears	5,000.00
Sheet2

Cell Formulas
Range	Formula
D2	{=IFERROR(INDEX($C$12:$C$15,MATCH(A2&"|"&B2,$A$12:$A$15&"|"&$B$12:$B$15,0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mumps]

Too late.

 

[Sean15]

Hi:

Thank you very much.

Could you give a solution that does not require Ctrl+Shift+Enter?



Regards,

Sean

 

[Fluff]

How about
=IFERROR(INDEX($C$12:$C$15,MATCH(1,INDEX((A2=$A$12:$A$15)*(B2=$B$12:$B$15),0),0)),"")

 

[Sean15]

How about
=IFERROR(INDEX($C$12:$C$15,MATCH(1,INDEX((A2=$A$12:$A$15)*(B2=$B$12:$B$15),0),0)),"")

Hi:

I don't need to refer to data in column B, (B2=$B$12:$B$15) so I changed formula to:

=IFERROR(INDEX($C$13:$C$16,MATCH(1,INDEX((A3=$A$13:$A$16),0),0)),"") but formula is returning ""

Could you fix please?

Regards,

Sean

 

[Fluff]

If you don't refer to the data in col B, you will end up with the same results your formula produced.

 

[Sean15]

Hi:

I am confused. Here is my sample worksheet and the required expected values.

Excel 2010
	A	B	C	D	E	F
1	Sheet1
2	Name	Type	Type	No sold	TL Sales	Expected value
3	Alpine	Pears	Pears	12		15,000.00
4	Alpine	Apples	Apples	10		-
5	Bata	Apples	Apples	24		12,000.00
6	Bata	Oranges	Oranges	52		-
7	Catnip	Pears	Pears	45		5,000.00
8	Catnip	Onions	Onions	62		-
9
10
11
12	Sheet2
13	Names	Sales
14	Alpine	15,000.00
15	Bata	12,000.00
16	Catnip	5,000.00
Sheet1 (2)

Cell Formulas
Range	Formula
E3	=IFERROR(INDEX($B$14:$B$16,MATCH(1,INDEX((A3=$A$14:$A$16),0),0)),"")
E4	=IFERROR(INDEX($B$14:$B$16,MATCH(1,INDEX((A4=$A$14:$A$16),0),0)),"")
E5	=IFERROR(INDEX($B$14:$B$16,MATCH(1,INDEX((A5=$A$14:$A$16),0),0)),"")
E6	=IFERROR(INDEX($B$14:$B$16,MATCH(1,INDEX((A6=$A$14:$A$16),0),0)),"")
E7	=IFERROR(INDEX($B$14:$B$16,MATCH(1,INDEX((A7=$A$14:$A$16),0),0)),"")
E8	=IFERROR(INDEX($B$14:$B$16,MATCH(1,INDEX((A8=$A$14:$A$16),0),0)),"")

 

[Fluff]

How can the formula be expected to know that Alpine & Pears is 15,000, but that Alpine & Apples is nothing?

 

[jtakw]

Hi,

Perhaps you mean you want to "Group" the results where you only want the result to display for the first instance of the Name?

If not, please answer Fluff's question above.

Book1
	A	B	C	D	E
1	Sheet1
2	Name	Type	No sold	TL Sales	Expected value
3	Alpine	Pears	12	15,000.00	15,000.00
4	Alpine	Apples	10	15,000.00
5	Bata	Apples	24	12,000.00	12,000.00
6	Bata	Oranges	52	12,000.00
7	Catnip	Pears	45	5,000.00	5,000.00
8	Catnip	Onions	62	5,000.00
9
10
11
12	Sheet2
13	Names	Sales
14	Alpine	15,000.00
15	Bata	12,000.00
16	Catnip	5,000.00
Sheet646

Cell Formulas
Range	Formula
E3	=IF(COUNTIF(A$3:A3,A3)=1,INDEX(B$14:B$16,MATCH(A3,A$14:A$16,0)),"")

Change/adjust cell references/range, add sheet name, as needed, formula copied down.