Get+more+information+here

VLOOKUP is a function in Excel. Your data must be in column formats for VLOOKUP to work. TIP: Remember when constructing the arguments for a VLOOKUP function, the fourth and final argument should be False (or 0) to return an exact figure. FALSE = EXACT MATCH

VLOOKUP Tutorial in Excel 2010 by Microsoft - get tutorial here.



Part 1
In this exercise, first, you’ll look up a part price. You’ll work in the Part Price spreadsheet. Look at the bottom of the spreadsheet to make sure the **Part Price** tab is selected. Look at the screen tip that’s displayed beneath the formula. Formula AutoComplete shows you that the first argument to enter is the **lookup_value**. That’s **what** you are looking for. You enter cell G2 because it is the cell into which you’ll eventually enter part numbers, which are in the left-most column of the list. Formula AutoComplete shows you that the **table_array** argument is next, which is also called the lookup table. That’s **where** you will look. That’s **which** column you want Excel to look for the search result. Finally, Formula AutoComplete indicates that the next argument you’ll enter the **range_lookup** argument, which is either True or False. An exact match=False, so double-click **False**,. Excel enters the argument into the formula for you. Type a closing parenthesis **)**. Press ENTER. Don’t worry about the #N/A error that appears in cell G3. You’ll get rid of the error as soon as you type a value in cell G2. Your formula should look like this: **=VLOOKUP(G2,A3****:D52,3,FALSE)** The result in cell G3 is $18.73.
 * 1) Click in cell G3. Type **=VLO**. **After you type the first two to three letters or so of VLOOKUP, Formula AutoComplete offers to help you with the formula. You should see VLOOKUP** [[image:http://officeimg.vo.msecnd.net/en-us/files/998/914/ZA102039267.jpg caption="VLOOKUP Formula AutoComplete"]]**beneath the cell you are typing in.** **Double-click the function name. After you do that, Excel inserts the function name and the beginning parenthesis into the formula.**
 * 1) Type **G2**, followed by a comma (**,**). The comma is essential, it separates the lookup_value argument from the next argument.
 * 1) In the formula, after the comma, type **A3:D52**, which are the cell references for the table_array.
 * 2) Next, type a comma (**,**) to separate the argument from the next argument. As soon as you type the comma, Formula AutoComplete shows you the next argument, which is the **col_index_num** argument.
 * 1) Count from the left, starting at column A with one, until you get to the Part Price column. That’s three.
 * 2) In the formula, type **3**, followed by a comma (**,**).
 * 1) In this case you want an exact match between the part number in the left column, and the part price in the third column. Do you know where the argument should be True or False?
 * 1) Place the cursor in cell G3, and look at your formula in the **Formula Bar** at the top of the spreadsheet.
 * 1) Click in cell G2, and type a part number from the left column. For example, type **A017**, and then press ENTER.

Part 2
Next, you’ll create another formula so that you can look up both part prices and part names at the same time. You’ll copy the first formula to create the second formula.
 * 1) First, you need to change the relative cell references to absolute cell references. The cell references G2,A3:D52 are relative references. That means that when you copy the formula, the cell references will change. For example, G2 will become G3, and A3:D52 will become A4:D53. That will give you an incorrect formula result. You need to make the cell references into absolute references that will not change when copied.
 * 2) Place the cursor in cell G3. In the Formula Bar, click either just before or in G2. Press the F4 key [[image:http://officeimg.vo.msecnd.net/en-us/files/672/706/ZA102026225.jpg caption="F4 key"]] on your keyboard. The cell reference changes from G2 to $G$2. The dollar signs ($) indicate an absolute cell references.
 * 3) Still in the Formula Bar, click either just before or in cell reference A3, and then press the F4 key [[image:http://officeimg.vo.msecnd.net/en-us/files/672/706/ZA102026225.jpg caption="F4 key"]] on your keyboard. The cell reference changes from A3 to $A$3.
 * 4) Finally, still in the Formula Bar, click either just before or in cell reference D52. Press the F4 key [[image:http://officeimg.vo.msecnd.net/en-us/files/672/706/ZA102026225.jpg caption="F4 key"]] on your keyboard. The cell reference changes from D52 to $D$52.
 * 5) With your cursor still in the Formula Bar, press ENTER to insert your changes into the formula.
 * 6) Right-click in cell G3, and then click Copy.
 * 7) Right-click in cell G4. Under Paste Options, click fx [[image:http://officeimg.vo.msecnd.net/en-us/files/221/918/ZA102039404.jpg caption="Formula Paste Options"]] for Formulas. Press the ESC key on your keyboard to get rid of the marque around cell G3
 * 8) Click in cell G4, and then in the Formula Bar, click inside the formula, just before “3.” The 3 is surrounded by two commas, like this “,3,”
 * 9) Type **2** (the part number column is the second from the left), and then press DELETE to get rid of “3.” Press ENTER. Now, instead of a part price in cell G3, it says Tie Rod End (if you typed A017 in cell G2). All you had to do was change the column number (the col_index_num argument) to get a different lookup result.
 * 10) In cell G3, type A024, and then press ENTER. You get the Part price in cell G3 ($45.19), and the part name in cell G4 (Tail Light Lens).

Exercise 2: Use absolute cell references in a VLOOKUP formula you copy down a column
In this exercise you’ll enter a VLOOKUP formula to look up the tax rates for different salaries. Double-click **True** as offered by Formula AutoComplete. Then type an ending parenthesis **)**. It’s true because you are looking for an approximate match. The salaries in column B don’t exactly match the salaries in column F, so you need an approximate match. Your formula should look like this: **=VLOOKUP(B2,F5:G24,2,TRUE)** Next, you need to copy the formula from cell C2 down the column, through cell C10. But first, you need to think about relative and absolute cell reference. Because the cell references to the tax table, F5:G24, are relative, when you copy the formula, the cell reference will change. For example, F5 will change to F6, F7, and so on. And G24 will change to G25, G26, and so on. That will give you incorrect formula results because the cell references must be “locked” to the tax table. You don’t want the references to the tax table to change. Remember, when you copy a formula down the column, use absolute cell references to lock the references to your lookup list or table. If you forget the F4 keyboard shortcut, you can type the dollar ($) signs in yourself.
 * 1) At the bottom of the spreadsheet, click the **Tax Table** tab to change the spreadsheet you’re working on.
 * 2) Click in cell C2. Type the equal sign (**=**), followed by the first few letters in VLOOKUP. Formula AutoComplete appears. Double-click VLOOKUP. Excel enters the function and the opening parenthesis in the formula. Type **B2** followed by a comma (**,**). You’ve just typed the **lookup_value** argument. You want Excel to look up the salary ($43,999) from cell B2 in the first (left-most) column of the tax table.
 * 3) Next, type **F5:G24** followed by a comma (**,**). This is your **table_array** argument. . You’re telling Excel where to look, which is in the Tax table. Excel will look for $43,999 in the left column, which is column F.
 * 4) Type **2** followed by a comma (**,**).This is the column in which Excel will look for the search result. It’s the **col_index_num** argument. Starting at one, which is column F (the left-most column of the tax table), you count to the next column, which is 2.
 * 5) Finally, enter the **range_lookup** argument, which is either True or False. Do you know which it should be for this formula?
 * 1) Press ENTER, which gives a result of 8% in cell C2. Click in cell C2, and then look at the formula in the Formula Bar at the top of the spreadsheet.
 * 1) With the cursor in cell C2, in the Formula Bar, click either just before or inside the cell reference F5. On your keyboard, press the F4[[image:http://officeimg.vo.msecnd.net/en-us/files/672/706/ZA102026225.jpg caption="F4 key"]] key. That changes the relative reference of F5 to $F$5. Now you’ve got an absolute reference. Next, still in the Formula Bar, click either just before or inside G24. Once again, press the F4[[image:http://officeimg.vo.msecnd.net/en-us/files/672/706/ZA102026225.jpg caption="F4 key"]] key on your keyboard. Now the reference G24 is absolute: $G$24. Your formula should look like this: **=VLOOKUP(B2,$F$5:$G$24,2,True)**. Notice that B2 is still a relative reference. That’s because you want B2 to change to B3, B4 and so on as you copy the formula down the column. You only lock the cells pointing to the tax table.
 * 2) With the insertion point still in the Formula Bar, press ENTER.
 * 3) Now you’ll copy the formula down the column. Click in cell C2 and point to the lower-right corner of the cell, until the pointer changes into a black plus sign [[image:http://officeimg.vo.msecnd.net/en-us/files/825/907/ZA102026176.jpg caption="Fill handle"]].
 * 4) Click and drag from cell C2 to cell C10. Release the mouse button. The tax rates are filled in. For example, Bott’s rate is 19%, and Phillips’ rate is 5%.

Exercise 3: Use a named range in a VLOOKUP formula
If you want to avoid creating absolute cell references when you copy a formula, you can use a named range instead. In this exercise you will define a name for the tax table, and then use that name in the formula. Remember that when you copy a formula you can either use absolute cell references or a named range.
 * 1) Place the cursor in cell F5. Click and drag from cell F5 to cell G5, and then drag down the list to cells F24 and G25. The entire list (excluding the headings) should be selected.
 * 2) Click in the **Name** box (where it says F5) above the spreadsheet. [[image:http://officeimg.vo.msecnd.net/en-us/files/450/044/ZA102039458.jpg caption="Name Box"]]
 * 3) In the **Name** box, type **TaxTable** (with no space between the words). Press ENTER.
 * 4) Click in cell C2. In the Formula bar, click inside the formula and delete **$F$5:$G$24**. Be sure to leave the commas as is.
 * 5) With the cursor after B2, type **TaxTable**. Press ENTER.
 * 6) Click in cell C2, and then look at your formula in the **Formula Bar**. It should look like this: **=VLOOKUP(B2,TaxTable,2,True)**
 * 7) Finally, you’ll copy the new formula from cell C2 to cell C10. Click in cell C2 and point to the lower-right corner of the cell, until the pointer changes into a black plus sign [[image:http://officeimg.vo.msecnd.net/en-us/files/825/907/ZA102026176.jpg caption="Fill handle"]].
 * 8) Click and drag from cell C2 to cell C10. Release the mouse button. The tax rates are filled in. Bott’s rate is still 19%, and Phillips’ rate is 5%. But if you click in any cell with a result in column C, you’ll see TaxTable in the formula (in the Formula Bar)**.**Note that the cell reference B2 will change to whatever cell the insertion point is in. For example, if you click in cell C9, the formula will look like this: **=VLOOKUP(B9****,TaxTable,2,True)**. While you want the cell references to the tax table to be “locked” or remain the same, in this example you want the cell references to column B to change as you copy the formula down the column.

Exercise 4: Use VLOOKUP on more than one spreadsheet
In this exercise you’ll type a formula on one spreadsheet. The formula will get information from a second spreadsheet, and return the result to the first spreadsheet. FALSE=Exact match. You want an exact match between page ID and page name. Don’t be alarmed by the #N/A error in cell F4. Excel is waiting for you to enter a value in cell F3.
 * 1) At the bottom of the spreadsheet, click the **Page Views** tab. The page has Page IDs, Page Views, and Hit Percentage for different pages on a Web site for September. But the names for each page are on the Pages tab. You can use VLOOKUP to get the names in a formula result on the Page Views tab. Then you can see which pages names got the most or least page views, and so on.
 * 2) Click the **Pages** tab. Take a look at the content. This will be your table_array or lookup table argument. Excel will look in the first, left-most column, and return a result (page name) from column 2, counting from the left, which will be your col_index_num argument.
 * 3) Click the Page Views tab again. Click in cell F4. Type the equal sign (**=**) and the first few letters of VLOOKUP to get Formula AutoComplete to start. Double-click the function name. Then type **F3** followed by a comma (**,**). That’s your **lookup_value**argument. In in other words, you’ll eventually enter a page ID into cell F3, and Excel will look up the page ID on the Pages tab to get the page name.
 * 4) Type **Pages!A2:B39** followed by a comma (**,**). This is your **table_array** or lookup table argument. The exclamation mark (!) after Pages indicates that you are referring to a separate spreadsheet called Pages. A2:B39 is the cell reference for the lookup table on the Pages tab.
 * 5) Type **2** followed by a comma (**,**). This is your **col_index_num** argument. Excel will look for the search result in the second column on the Pages spreadsheet.
 * 6) Double-click **FALSE** and then type an ending parenthesis **)**, and press ENTER. This is your **range_lookup** argument. Do you recall why the argument would be false in this example?
 * 1) Click in cell F4, and then look at the formula in the Formula bar. It should look like this: **=VLOOKUP(F3,Pages!A2:B39,2,FALSE)**
 * 1) Click in cell A7. Press CTRL+C to copy the page ID. Click in cell F3 and press CTRL+V to paste the ID into the cell. The result in cell F4 is International News 2. Press ESC on your keyboard to get rid of the marquee around cell A7.

Exercise 5: Use VLOOKUP to look up a discount
In this example, we don’t want to limit Excel to exact matches, so double-click **True** and then type an ending parenthesis **)**. Press ENTER. Click in cell F21 and then look at the formula in the Formula Bar. It should look like this: **=VLOOKUP(F20,B21:C25,2,TRUE)**. You’ll see a #N/A error in cell F21, because Excel is waiting for you to enter information into cell F20.
 * 1) At the bottom of the page, click the **Discount** tab.
 * 2) Click in cell F21. Type an equal sign (**=**) and the first few letters of VLOOKUP. Double-click the function name to enter it into the formula. Then type **F20** followed by a comma (**,**). Cell F20 is your **lookup_value**argument. Eventually you will enter a value into cell F20, and Excel will look for that value in the first, left-most column of the list.
 * 3) In the formula, type **B21:C25** followed by a comma (**,**).This is your **table_array** or lookup table argument. The cell range encompasses the Excel list.
 * 4) Type **2** followed by a comma (**,**). This is your **col_index_num** argument. Counting from the left (the Quantity column), the Discount column is 2. That’s the column from which Excel will return a search result.
 * 5) Would the **range_lookup** argument be True, or False?
 * 1) Type any quantity you want into cell F20. For example, **10999** and then press ENTER. That gets a 12% discount. The formula can see that 10999 is greater than 10000, but there is no higher discount than 12%.
 * 2) Click in cell F20, type **99** and then press ENTER. You’ll see a 3% discount. The formula can see that 99 is more than 10, but less than 100 when a 6% discount kicks in, so the discount is 3%.
 * Important** Do you notice that the left-most column is in ascending order? When you use True for the range_lookup argument, the first column must be in ascending order from smallest to largest, or from A to Z.

Bonus exercise: Create an Excel table
I mentioned in the course that you could create a table in the exercises if you’d like to. For more information about the advantages of Excel tables, search for the Help topic “Create or delete an Excel table in a worksheet.” The Excel list is converted into an Excel table with cell formatting. Filtering arrows are automatically added to the header rows, making it easy to sort information.
 * 1) Click in any cell within the list. For example, click in cell C22.
 * 2) On the ribbon, click the **Insert** tab, and in the **Tables** group, click **Table**.
 * 3) In the **Create Table** dialog box, be sure that the **My table has headers** box is checked, and notice the marquee around the entire table. Click **OK**.