INDEX & SUMPRODUCT to return a text value

[ddub25]

[TABLE="width: 236"]
<tbody>[TR]
[TD]W1-A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]W1-A[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]Strawberries[/TD]
[/TR]
[TR]
[TD]W1-A[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]Raspberries[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 272"]
<tbody>[TR]
[TD]W1-A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]W1-A[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]W1-A[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]W1-A[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]Strawberries[/TD]
[/TR]
[TR]
[TD]W1-A[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]Raspberries[/TD]
[/TR]
[TR]
[TD]W1-A[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]W1-A[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]W1-A[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]Clementines[/TD]
[/TR]
[TR]
[TD]W1-A[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]Satsumas[/TD]
[/TR]
</tbody>[/TABLE]


I am trying to create a formula for the first table to be placed in column 4, that can look at it's 3 values to the left in columns A, B and C and find a match in the 2nd table. In this example, Table 1, A4 would return 'Apples' as it immediately finds a match for 'W1-A', '1' & '1' in the 1st row of Table 2. The 2nd row of Table 1 returns Strawberries as it finds it's matches in Row 4 of Table 2.

I am getting odd results using an INDEX & SUMPRODUCT functions. Can anyone help?

P.S. My table may not start in row 1 of the spreadsheet

 

[jtakw]

Hi,

You can use the INDEX/MATCH Array formula to be confirmed by CSE (Control, Shift, Enter), instructions below,
Or, Non-Array, normally entered, LOOKUP formula:

Book1
	A	B	C	D	E
1	W1-A	1	1	Apples	Apples
2	W1-A	1	2	Strawberries	Strawberries
3	W1-A	1	3	Raspberries	Raspberries
4
5
6	W1-A	1	1	Apples
7	W1-A	2	1	Pears
8	W1-A	3	1	Bananas
9	W1-A	1	2	Strawberries
10	W1-A	1	3	Raspberries
11	W1-A	3	2	Kiwi
12	W1-A	4	1	Oranges
13	W1-A	5	1	Clementines
14	W1-A	6	1	Satsumas
Sheet434

Cell Formulas
Range	Formula
E1	=LOOKUP(2,1/((A$6:A$14=A1)*(B$6:B$14=B1)*(C$6:C$14=C1)),D$6:D$14)
D1	{=INDEX(D$6:D$14,MATCH(A1&"|"&B1&"|"&C1,A$6:A$14&"|"&B$6:B$14&"|"&C$6:C$14,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Change/adjust cell references/range as needed.

 

[ddub25]

Thanks Jtakw, thats very useful, problem solved. Thanks

Dan

 

[jtakw]

You're welcome.