CLICK HERE FOR THE SOURCE
CLICK HERE FOR THE SOURCE
VLOOKUP and MATCH
These are your way of asking excel to find a needle in haystack. Imagine you have all your customer contact information in one sheet in the range A1:D5000 in the format phone number, name, city and date of birth. Now you need to find out which customer has the phone number “936-174-5910″. How do you do it?
You guessed it right, you use VLOOKUP and summon excel to do the search and return with customer name.
As September approaches, I can count on a series of spreadsheet questions. One of the more popular Excel tutorial requests is how do you look up a value on one Excel worksheet and use it on another Excel worksheet. For example, you need to translate a product number into a product name. One of my favorite Excel functions is the VLOOKUP function and it can help with this task. (Includes Excel VLOOKUP example spreadsheet.)
A recent case involved some voter registration data I needed to analyze. On one Excel spreadsheet, the voter’s party was listed as an alphanumeric value called "Pcode" and not the political party. This coding wasn't intuitive. For example, “D” was for “American Independent Party”, but some thought it meant “Democratic Party”.
Excel-spreadsheet-with-column-needing-lookup
Click to enlarge
One way I solve this problem is to create a worksheet with the Pcode and translation and I have Excel use the VLOOKUP function for the party name. You might think of VLOOKUP as an Excel translator. I could then add a column called “Political Party” to my original worksheet to show the information from a lookup table.
Creating a Lookup Table
A lookup table includes the values you wish to "lookup" such as our Pcode and the translation such as political party. You can place this table on the same worksheet, but for this Excel tutorial we'll add a worksheet called "Political Party".
How to Create a Lookup Table,
1. Right-click your spreadsheet’s tab and select Insert…
2. On the Insert dialog, double-click Worksheet. This will be on the General tab.
3. Rename this new worksheet tab with a descriptive name such as “Party Codes”
4. In Column A, enter the unique values that exist on your main worksheet. In my example, these were the codes that showed in the Pcode column in the thumbnail. These values should be in ascending order.
5. In Column B, enter the translated value. You can have more values in column A than appear on your main spreadsheet. For example, I have an entry for “Citizen Party” even though I didn’t show a registered voter with that affiliation.
Excel-worksheet-used-as-a-lookup-table
Click to enlarge
Using the VLOOKUP Function
Excel’s VLOOKUP function uses 4 pieces of information. The function panel may seem intimidating with the terms, but it’s simpler than it looks.
To lookup a value using VLOOKUP,
1. Add your new column on your original worksheet that will display the info pulled from the Lookup table. In my example, I added a column called Political Party in Column D. This is where I will insert the Excel function.
Excel-worksheet-with-added-column-for-VLOOKUP
Click to enlarge
2. Place your cursor in the first blank cell in that column. In my example, this is cell D2.
3. From the Insert menu, select Function…. The Insert Function dialog will appear.
Excel-Insert-Function-dialog
Click to enlarge
4. In the Search for a function: text box, type “vlookup” and click Go.
5. Highlight VLOOKUP and click OK.
Defining the VLOOKUP Values
After you click OK, Excel’s Function Arguments dialog appears and allows you to define the four values. You’ll see that your starting cell and the formula bar show the beginning part of the function =VLOOKUP(). The Function Arguments dialog adds the needed data elements that will display between ().
For illustration purposes, I have overlaid the Party Codes worksheet on top to show the relationships.
Overlay-of-VLOOKUP-function-agruments-dialog-and-worksheets
Click to enlarge
1. Lookup_value – Think of this field as your starting point. In my example, I’ll click cell C2 so the value is filled in the dialog. I'm requesting Excel take the value of C2, which displays as the Pcode of “A”, and find the matching political party on my lookup table on the Party Codes worksheet.
2. Table_array – This is the range for your lookup table. The range can be on your existing worksheet or another worksheet such as our “Party Codes”. When you click another tab and define the range, Excel prepends that tab name to the range such as ‘Party Codes’.
Rules & Caveats
There are several rules to remember about this table array.
Rule 1 - The left column must contain the values being referenced. In other words, I couldn’t have our first column be Political Party.
Rule 2 - You can’t have duplicate values in the leftmost column of the lookup range. I couldn’t have two entries with the value “A” with one being “Democratic” party and another “A” for the “Humanist” party. Excel would complain.
Rule 3 - When referencing a lookup table, you don’t want your cell references to change when you drag and fill to populate the other cells with the VLOOKUP function. As example, if I want to use the same function in cells D3 through D7, I don’t want my lookup cell references to shift each time I move down to the next cell. I need the cell references to be the same. After you define your range, you need to press F4 which will cycle through absolute and relative references. You want to select the option that includes a $ before your Column and Row. ( 'Party Codes'!$A$2:$B$45. ) You can get around this if you know how to use Excel name ranges.
Col_index_num – This is the number of the column on your lookup table that has the information you need. In our example, we want column 2 from the Party Codes worksheet which has the name of the political party.
Range-lookup – this field defines how close a match should exist between your Lookup_value (C2) and the value in the leftmost column on our lookup table. In our case, we want an exact match so we’ll use “FALSE”.
After clicking various cells, my dialog looks like this:
VLOOKUP-Function-arguments-dialog-with-formula-result
Click to enlarge
You can see in the circled formula bar above, I now have more information based on my entries in the Function Arguments dialog box.
The other item of interest is that when you build these functions, Excel displays the result in the Formula result = text line. This is great feedback which can show if your function is on target. In our example, we can see Excel looked up the Pcode of “A” and returned the Political Party “Democratic”.
Copying the VLOOKUP Function to Other Cells
It doesn’t make sense to use VLOOKUP for one cell in your Excel spreadsheet. Instead, I want to copy the function to other cells in the same column.
To copy VLOOKUP to other column cells,
1. Click the cell containing the VLOOKUP arguments. In our example, this would be D2.
2. Grab the cell handle that displays in the lower right corner.
3. Left-click and drag down the cell handle to cover your column range.
Note: If I hadn’t changed to absolute reference as mentioned in Rule 3, I would’ve seen my table array entry shift by one cell as we dragged down through the other cells.
VLOOKUP is a powerful Excel function that can leverage spreadsheet data from other sources. There are many ways you can benefit from this function. In this example, I used a 1:1 code translation, but you could also use it for group assignments. For example, I could assign state codes to a region such as CT, VT, and MA to a region called “New England”. And for the adventurous, you can use VLOOKUP in your formulas.
While VLOOKUP is used to fetch value a based on what you are looking for, MATCH is used to fetch the position of the value you are looking for.
OFFSET is your way of telling excel to fetch a portion from large range of values. You can compare OFFSET to what you see from your car window while driving. As your car moves, you see different things from the window.
OFFSET returns a reference to the portion of a large range you have supplied based on 5 parameters. For eg. OFFSET (A1, 3,4, 5,6) would return 5×6 cell range from E4 (A+4 columns, 1+3 rows = E4) thus: E4: J9
So how are Offset() and Match() linked to each other?
Since MATCH returns the position of the item you are looking for in a list, you can then use this position in OFFSET to fetch values surrounding the searched value.
Finally
Remember, both VLOOKUP and MATCH throw a fail error of #value! if the value you are looking for is not there. Also, OFFSET returns a range so make sure you pass the value to another function like SUM that accepts ranges.
Concatenate function
Allows you to join 2 or more strings together.
The syntax for the Concatenate function is:
Concatenate( text1, text2, ... text_n )
There can be up to 30 strings that are joined together.
BISWA in one column can be joined with JIT in another to form BISWAJIT.
No comments:
Post a Comment