Index & Match ?

[Vee74]

Hi,

I'm trying to populate a table of data using criteria in Rows and Columns, not sure if Index & Match is the best formula to use, and if it is how to construct it correctly.

Below is how my data sheet looks with the first 2 rows and first 2 columns being criteria, with data in the red. Columns 3 & 4 contain irrelevant data that I want to ignore.

				1	2	1	2
				A	A	B	B
105	X			3	0	1	1
105	Y			1	5	14	3
210	X			0	2	9	1
210	Y			10	0	5	1

<tbody>
</tbody>

Can I put a formula in a second sheet that matches the criteria in the two columns and the two rows. In the FORMULA cell below it would return the value 14 from the table above.

		1
		B
105	Y	FORMULA

<tbody>
</tbody>

Thanks in advance,
V.

 

[AlanY]

try this

Excel 2013/2016
	A	B	C	D	E	F
1			1	2	1	2
2			A	A	B	B
3	105	X	3	0	1	1
4	105	Y	1	5	14	3
5	210	X	0	2	9	1
6	210	Y	10	0	5	1
7
8
9			1
10			B
11	105	Y	14
Sheet1

Cell Formulas
Range	Formula
C11	=SUMPRODUCT(((C1:F1&C2:F2=C9&C10))*((A3:A6&B3:B6=A11&B11))*C3:F6)

 

[Vee74]

Thank you. That works.

 

[AlanY]

you're welcome