Alternate Article: Excel vlookup example
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:
Created by Clifford MacKay at 9/30/2009 3:11:14 PM
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.
We start with two sheets in Excel:
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.
At this point we do several searches and replaces.
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:
Note: this article is also publicly available at http://wiki.wws5.com/share/How To Use A Lookup in excel for adding categories0909301511.html