Quick Reference for VLOOKUP
Follow these steps to successfully complete a VLOOKUP formula in MS Excel.
Download the KBART of the entire collection from the “more Actions” drop down menu in the title accordion within the collection.
There is often an identifying value in the title list that matches with the Title ID in the KBART
Open the tab-delimited KBART file from Collection Manager in Excel. Open a second sheet to copy the identifying value (Book ID, Product ID, Document ID, etc) and titles from the title list.
Insert a blank column next to the unique identifier in the second sheet, place an “x” in all the relevant cells in the blank column.
Insert a blank column next to the Title ID in the KBART (Column M)
Select Formula tab, select the first cell in the column you created in the KBART, click “Insert Function,” and use the Formula Builder to create the VLOOKUP formula
With the cursor in Lookup_value in the Formula Builder, select the cell to the left of where the formula is inserted in the KBART (L2 if using Title_ID as matchpoint).
Place the cursor in the table_array field in the VLOOKUP Formula Builder, select the second sheet containing your title list, and highlight the two columns that contain the values you are looking for as well as the “x” or other marker being used.
In the Formula Builder, set the col_index_num to 2, and the range_lookup to false.
Press enter, go into the function box and add “$” before and after the column letters. For example: “=VLOOKUP(L2,Sheet1!$A$2:$B$132,2,FALSE)”.
Press enter again to refresh the formula.
Copy formula into the rest of the cells in the column. Those that are matches will have an “x” in the cell. Those without a match will have “#N/A”.
Sort the sheet by Column M to group the matches (if using title_id as matchpoint).
Delete out rows with “#N/A” in the title_id column.
Delete out the column with the “x” (KBARTs should only have 26 columns total, A through Z).
Save as tab-delimited text file.
Upload into the collection using “More Actions” drop down menu in the title accordion within the collection
Note that when uploading a KBART file into a collection there are two scope options, “Update changes only” and “Replace holdings in collection.” If a KBART is uploaded with “Update changes only” scope option, it will add the titles in the KBART unless the ACTION is overlay. If a KBART is uploaded with the “Replace holdings in collection” option, whatever is selected in the collection will first be deselected, and then the titles in the KBART will be selected.
Once the file has been loaded into the collection, check back with the collection to approve or deny the KBART upload. This should be within 3 days of uploading the KBART file. The collection will then lock for re-indexing and load the titles.
To determine holdings from EBSCONET orders, you will need an account to log in and review order details and Access information.
You will need your customer code, username, and password to log in:
Log in to your OCLC services, WorldShare Collection Manager(WMS).
Steps to add holdings of an eJournal or eBook in Collection Manager.
In WMS, navigate to Metadata --> Collection Manager.
Change Search
For this example, we are looking for the Collection: Ingenta Connect
The Selections column will display a no symbol in red or a Selected symbol in green, which signifies if the item is selected or not.
If it is not selected, click the Select Title button below Not selected by my library in the Selections column.
Once it is selected, also click on the pencil link to update holdings for the OCLC record.
That might be all you need to do, but if you need to update the MARC record or holdings range, then customization is required.
Click the Title Link in the Collection Manager, then click the Local tab
Click the breadcrumb link to get back to the search list and click Website under the Identifiers column to verify your title is functioning properly.
Also search in the WorldShare Discovery catalog to verify the item is turned on as expected.
How to remove a eBook from a Print record when the year or version does not match.
This scenario describes what is called a title mismatch.
For the title Nursing the ultimate study guide in our catalog.
There are two records for this book 2016 and 2013/12 and they both have an eBook link to the 2013/12 ebook.
I have not been able to remove the eBook from the 2016 record successfully.
, English
The collection manager contains only the OCLC number for the ebook 2013/12 record. 826658523.
Dummy Collection Fix:
This will remove the ebook from the 2016 copy.