Video Document - training: howtousealookupinexcelforaddingcategories0909301511

PortalDocument Searchtraining: howtousealookupinexcelforaddingcategories0909301511

Parent Article(s):

IMPORTANT CHANGE

I have been informed of a very cool feature in Microsoft Excel that allows us to avoid having to do all that searching and replacing to make the formula work. If we place a "$"-sign in parts of the formula that we want to be static, then those parts of the formula will not change as we copy the formula down the list. If you click F4 while having a cell like location like "B2" selected it will automatically change it to "$B$2".

The new formula that will copy down the list from the above example is:

<code>

= LOOKUP(B2,SHeet2!$B$2:$B$1001,Sheet2!$A$2:$A$1001)

</code>

Created by Clifford MacKay at 9/30/2009 3:11:14 PM

Problem Description

How to use a Microsoft Excel Lookup:

----------------------------------------------

Purpose: Allow an Excel user to merge data from two Excel worksheets in order to add the category from one inventory list to another list that is missing 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 an example where we lookup the category from a second sheet as a way to merge information into the current sheet that is missing categories.

Solution

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 2003 supports between 64,000 and 65,000 rows, Excel 2007 and higher supports over a million rows).

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

    • Video source: training:howtousealookupinexcelforaddingcategories0909301511.flv