copy cell if two criteria match

[Fazila]

OK I have a table that looks something like this:

[TABLE="width: 100"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Surname[/TD]
[TD]Forename[/TD]
[TD]UPN[/TD]
[TD]Type[/TD]
[TD]Points[/TD]
[TD]Talking when the teachers talking[/TD]
[TD]Equipment[/TD]
[TD]Homework[/TD]
[TD]Disruption to Learning[/TD]
[TD]Attitude to Learning[/TD]
[/TR]
[TR]
[TD]Aashir[/TD]
[TD]Muhammad[/TD]
[TD]DXxxx[/TD]
[TD]Talking when the teachers talking[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]DXxxx[/TD]
[TD]Equpment[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Abdillahi[/TD]
[TD]Yaqub[/TD]
[TD]EXxxx[/TD]
[TD]Homework[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]EXxxx[/TD]
[TD]Disruption to learning[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

On another sheet I have a table that has the UPN and columns F to J. If the UPN matches and the type matches the column headings then I want the points to go into the relevant column.

Really hope this makes sense, any thoughts on how I can do this? VBA or formula would be really helpful.

Thanks

 

[Special-K99]

I dont think this is what you want but you only said for the figure to go into the relevant COLUMN, nothing about ROW

1. Correct your mistake in D3, it says Equpment and does not match G1 (Equipment).

2. in F2
=IF(COLUMN()-5=MATCH($D2,$F$1:$J$1,0),$E2,"")
copy acrosss and down the table

 

[Fazila]

I dont think this is what you want but you only said for the figure to go into the relevant COLUMN, nothing about ROW

1. Correct your mistake in D3, it says Equpment and does not match G1 (Equipment).

2. in F2
=IF(COLUMN()-5=MATCH($D2,$F$1:$J$1,0),$E2,"")
copy acrosss and down the table

Sorry Special-K99 couldn't get this to work.

To clarify I have a spreadsheet which has anything between 1 to 20 rows per student each row has a certain type of behaviour incident and the number of points allocated. For each year group I have a data sheet and a behaviour sheet. My data sheet consists of say:

Surname
Forename
UPN
All the different types of behaviour incidents (in separate columns so column D to AP), a sample of which has been posted above.

in the Data sheet for each student I want to know the number of behaviour points they have under each category so Muhammed Aashir has 1 behaviour point for Talking when the teachers talking and 1 behaviour point for Equipment. On the Data sheet therefore in row 2 I need column D and E to have the number 1.

I need the UPNs to match so the data is pasted against the correct student as in the Behaviour sheet 1 student could have any number of rows.

Really hope this make sense and thank you for helping.

 

[Fazila]

=INDEX('Year 7 Behaviour'!$E$2:$E$2661,MATCH('Year 7 Data'!$C2,'Year 7 Behaviour'!$C$2:$C$2661,0),MATCH('Year 7 Data'!Q$1,'Year 7 Behaviour'!$D$2:$D$2661,0))

This formula works but only for the first column when I try in the next category it returns #REF !

 

[Fazila]

I think my query is very similar to this - I have looked at the macro but really can't make any sense of it

https://www.mrexcel.com/forum/excel...ia-two-sheets-then-copy-paste-cell-value.html

 

[Fazila]

this formula worked thought I would post for future reference

=IFERROR(INDEX(BehaviourPoints,MATCH(1,($C2=UPNBehaviour)*(Q$1='Year 7 Behaviour'!$D$2:$D$2661),0)),"")