Skip to main content
OCLC Support

How can I utilize Excel's VLookup

Symptom
  • I need to locate a specific file entry or I want to compare files
Applies to
  • WorldCat Collection Manager
Resolution

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 the Activity History to confirm the file successfully uploaded.

Follow these steps to successfully complete a VLOOKUP formula in MS Excel.

Quick Reference to performing VLOOKUP:

1. Download the KBART of the entire collection from the “More Actions” drop-down menu in the title accordion within the collection.

2. There is often an identifying value in the vendor sheet that matches with the TitleID in the KBART.

3. Open the tab-delimited 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 vendor sheet into the second sheet in Excel

4. Insert a blank column next to the BookID in the second sheet, place an “x” in all the relevant cells in the blank column

5. Insert a blank column next to the TitleID in the KBART (will be Column M)

6. Using the Formula Builder to create the VLOOKUP

7. Using the Wizard, select L2—the value to the left of the cell in which the formula is input.

8. Select the second sheet, then place the cursor in the table_array field in the VLOOKUP wizard and select the relevant cells in the second sheet

9. col_index_num is 2

10. range_lookup = False to indicate you want a perfect match

11. Press enter, go into the function box, and add “$” before “A,” “2,” “B,” and the last row (in the example "=VLOOKUP(L2,Sheet1!$A$2:$B$132,2,FALSE)")

12. Press enter again to refresh the formula.

13. Copy the formula into the rest of the cells in the column. Those that match will have an “x” in the cell. Those without a match will have "#N/A”

14. Sort the sheet by Column M to group the matches

15. Delete out rows with “#N/A” in the title_id column

16. Delete out the column with the x (KBARTs should only have 26 columns total, A-Z)

17. Save as tab-delimited text file

18. Upload into the collection using “More Actions” drop-down menu in the title accordion within the collection

Check activity history to see that file successfully uploaded

 

Page ID
37123