How to use an excel Lookup:
Purpose: Allow an excel user to merge two excel sheets in order to add the category from one inventory list to another list without categories. This can be applied to any sort of field where a matching field exists.
There are limitations to this procedure and they are illustrated near the end of the video.
In this video we use and example where we lookup the category from a second sheet as a way to merge information into the current sheet without categories.
We start with two sheets in excel:
* The first sheet contains a list of part numbers and quantities, but do not have a category
* The second sheet contains an export of all parts from system five with category, part number, and an additional field description.
We use the formula called "Lookup" in excel to accomplish this. Since the lists may be very long we illustrate how to perform the same actions on a very large list of items. (Excel supports between 64,000 and 65,000 records).
There is a multiple step process or work around to make the entry of the formula quick to enter. We talk you though the hows and why's as we do the video. We start by reviewing the initial formula we are looking to write. We don't start off using this formula though.
* Formula Example: = LOOKUP(B2,SHeet2!B2:B1001,Sheet2!A2:A1001)
* We start by using this modified formula: = CLEAN(B2&SHeet2!B2:B1001,Sheet2!A2:A1001)
At this point we do several searches and replaces.
* =Clean( replaced by Lookup(
* & replaced by ,
* " replaced by (blank)
* Lookup( replaced by =Lookup(
Additionally we do an instert and a copy followed by a paste special to set the changes in stone. Don't forget to save now.
Problems we can run into:
* Duplicates will only look up the first occurance in sheet 2.
* Information in Sheet2 needs to be complete, all parts in sheet 1 need to exist in Sheet2.