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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]allocated site[/td][td]id[/td][/tr]


[tr][td]

2​

[/td][td]a[/td][td]

1​

[/td][/tr]


[tr][td]

3​

[/td][td]x[/td][td]

2​

[/td][/tr]


[tr][td]

4​

[/td][td]b[/td][td]

1​

[/td][/tr]


[tr][td]

5​

[/td][td]y[/td][td]

3​

[/td][/tr]


[tr][td]

6​

[/td][td]q[/td][td]

7​

[/td][/tr]


[tr][td]

7​

[/td][td]c[/td][td]

4​

[/td][/tr]


[tr][td]

8​

[/td][td]d[/td][td]

5​

[/td][/tr]
[/table]

Sheet1

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]preferred site[/td][td]id[/td][td]

4​

[/td][/tr]


[tr][td]

2​

[/td][td]a[/td][td]

1​

[/td][td][/td][/tr]


[tr][td]

3​

[/td][td]a[/td][td]

2​

[/td][td]x[/td][/tr]


[tr][td]

4​

[/td][td]b[/td][td]

1​

[/td][td][/td][/tr]


[tr][td]

5​

[/td][td]c[/td][td]

3​

[/td][td]y[/td][/tr]


[tr][td]

6​

[/td][td]Q[/td][td]

7​

[/td][td][/td][/tr]


[tr][td]

7​

[/td][td]c[/td][td]

4​

[/td][td][/td][/tr]
[/table]

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))))