Best formula for VLOOKUP and getting matching results from 2nd worksheet? (MATCH might not be best)

[kznmrexcel]

Hello,

I'm working with a file that has two worksheets, one called "studentList," and another called "library." On the studentList worksheet, the data might be old, having been downloaded from a separate information system. My task is to compare the list of owed books on the "studentList" worksheet to the "library" worksheet list, which is current.

Student ID numbers appear in column A on both worksheets. I would like to do something like this on the studentList worksheet in a new cell for row 2:
= IF(VLOOKUP(A2,library!$A$1:$L$2000,4=D2),4,FALSE) but this formula doesn't work.

If the student whose ID number appears in A2 has a specific book (example, "Holes") in D column on the worksheet "studentList" and the same book appears on the worksheet, "library" for that student ID number, I would like to return the value in E column.

Here's the rub: I only want to see "Holes" return on the row where it's shown on "studentList" because Joe Blow might be on the studentList twice, once for "Holes" and once for "Watership Down." If Joe is listed for a third book, "Charlotte's Web," on the studentList worksheet but NOT on the library worksheet, I don't want "Holes" to show up instead.

I would like to get this result:

	A	B	C	D	E
1	ID	Last	First	Old list – this worksheet	Library list from second worksheet
2	1234	Schmoe	Joe	“Holes”	“Holes”
3	1234	Schmoe	Joe	“Watership Down”	“Watership Down”
4	1234	Schmoe	Joe	“Charlotte’s Web”	#NA

<tbody>
</tbody>

Not:

	A	B	C	D	E
1	ID	Last	First	Old list	Library list from second worksheet
2	1234	Schmoe	Joe	“Holes”	“Holes”
3	1234	Schmoe	Joe	“Watership Down”	“Holes”
4	1234	Schmoe	Joe	"Charlotte's Web"	"Holes"

<tbody>
</tbody>

Any ideas?

Thanks,
Karen

P.S. The red text is only present to show the current problem. I don't need colored text for results.

 

[kznmrexcel]

I just re-read my post and realized that, for consistency, I should have named my sample student Joe Schmoe throughout. My apologies. Substitute "Schmoe" for "Blow"...
<

 

[AhoyNC]

See if this works.
Copy formula in E2 down as needed.

This is an array function and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1	ID	Last	First	Old list this worksheet	Library list from second worksheet
2	1234	Schmoe	Joe	Holes	Holes
3	1234	Schmoe	Joe	Watership Down	Watership Down
4	1234	Schmoe	Joe	Charlottes Web	#N/A
5	2345	Blow	Joe	Charlottes Web	Charlottes Web
StudentList

Excel Workbook
	A	B	C	D
1	ID	Last	First	Library list
2	1234	Schmoe	Joe	Holes
3	1234	Schmoe	Joe	Watership Down
4	2345	Blow	Joe	Charlottes Web
library

 

[kznmrexcel]

I'm on a Mac with OS 10.10.2 and Excel 2011. I copied and pasted the entire formula, shown above, into cell E2, but it didn't work. I tried pasting it and then removing the curly braces, but still no. I also tried pasting it and then typing CTRL+SHIFT+ENTER. Nada. Is there something else to try that I left out?

 

[AhoyNC]

I think to enter an array formula on a Mac you use CMD-Return.
Excel will put the curly braces around the formula for you (you don't want to type them in).
Also changes the ranges in the formula to match your data.