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

kznadalin

New Member
Joined
Jun 16, 2010
Messages
40
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:
ABCDE
1IDLastFirstOld list – this worksheetLibrary list from second worksheet
21234SchmoeJoe“Holes”“Holes”
31234SchmoeJoe“Watership Down”“Watership Down”
41234SchmoeJoe“Charlotte’s Web”#NA

<tbody>
</tbody>

Not:
ABCDE
1IDLastFirstOld listLibrary list from second worksheet
21234SchmoeJoe“Holes”“Holes”
31234SchmoeJoe“Watership Down”
“Holes”
41234SchmoeJoe"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. :)
 
Last edited:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

kznadalin

New Member
Joined
Jun 16, 2010
Messages
40
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1IDLastFirstOld list this worksheetLibrary list from second worksheet
21234SchmoeJoeHolesHoles
31234SchmoeJoeWatership DownWatership Down
41234SchmoeJoeCharlottes Web#N/A
52345BlowJoeCharlottes WebCharlottes Web
StudentList



Excel Workbook
ABCD
1IDLastFirstLibrary list
21234SchmoeJoeHoles
31234SchmoeJoeWatership Down
42345BlowJoeCharlottes Web
library
 

kznadalin

New Member
Joined
Jun 16, 2010
Messages
40
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,595
Messages
5,597,078
Members
414,121
Latest member
DamianX88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top