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