Look ups or Index Match Match question

[b19upj]

Hi,

I have 2 spreadsheets.
The first one has 2 columns
Column 1 is a 'Task Number'
Column 2 is a 'Product Number'

On my other spreadsheet i have the Task numbers running in each column and the product numbers running down the rows to form a sort of empty table/matrix.

How can i look up the figures of the column task number with the row product number from the first spreadsheet and where there is a match for both enter the value "1" therefore populating my table/matrix

TIA

 

[Andrew Poulsom]

Formula in E2 copied down and across:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Task	Product			Task1	Task2	Task3	Task4	Task5
2	Task1	Product1		Product1	1
3	Task2	Product2		Product2		1
4	Task3	Product3		Product3			1
5	Task4	Product4		Product4				1
6	Task5	Product5		Product5					1
Sheet1

Cell Formulas
Range	Formula
E2	=IF(ISNUMBER(MATCH(1,INDEX(($A$2:$A$6=E$1)*($B$2:$B$6=$D2),),FALSE)),1,"")

 

[b19upj]

Hi Andrew,

Thanks very much for your reply.
For one reason or another it doesn't seem to be working for me.
I suspect its something wrong my end.

I have thought up a way to do it so thought i'd share.
I simply made a pivot from the 2 columns and then plotted Task as the X, Products as the Y and then a count of the products in the values box.
This produced the matrix i was looking for.

Thanks again

Jason