VLOOKUP to compare 2 spreadsheets?

[AdamJones]

Hi,

I have to compare 2 sheets for work, they are as follows:

Sheet 1:
Column A: Preferred Site
Column B: Student ID Number
Column C: Surname
Column D: Forename

Sheet 2:
Column A: Allocated Site
Column B: Student ID Number
Column C: Surname
Column D: Forename

I need to find out how many people from sheet 2 were allocated to their preferred site from sheet 1. I've tried using vlookup but the way I'm entering it must be wrong? If I choose the first selection as ID from sheet 2, the range as all columns from sheet 1, then 1, and false?

Can anybody help?

Much Appreciated

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH(Sheet2!$B$2:$B$8&"|"&Sheet2!$A$2:$A$8,$B$2:$B$7&"|"&$A$2:$A$7,0)),1))

 

[AdamJones]

Thanks that's great, but I also need to see where people where allocated if it wasn't their preference so I think I need vlookup?

 

[Aladin Akyurek]

Thanks that's great, but I also need to see where people where allocated if it wasn't their preference so I think I need vlookup?

Sheet2

Row\Col	A​	B​
1​	allocated site	id
2​	a	1​
3​	x	2​
4​	b	1​
5​	y	3​
6​	q	7​
7​	c	4​
8​	d	5​

Sheet1

Row\Col	A​	B​	E​
1​	preferred site	id	4​
2​	a	1​
3​	a	2​	x
4​	b	1​
5​	c	3​	y
6​	Q	7​
7​	c	4​

In E4 control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH(Sheet2!$B$2:$B$8&"|"&Sheet2!$A$2:$A$8,
    $B$2:$B$7&"|"&$A$2:$A$7,0)),1))

This means: For 4 id's the sites allocated to them match the sites they prefer. Given the fact that we have here 6 records, 2 times the expected correspondence do not materialize as displayed below:

In E2 control+shift+enter, not just enter, and copy down:

=IF($B2="","",IF(ISNUMBER(MATCH($A2&"|"&$B2,Sheet2!$A$2:$A$8&"|"&Sheet2!$B$2:$B$8,0)),"",
    INDEX(Sheet2!$A$2:$A$8,MATCH(1,IF(Sheet2!$B$2:$B$8=$B2,IF(1-(Sheet2!$A$2:$A$8=$A2),1)),0))))