Meh study tentang MS Excell, VLOOKUP & HLOOKUP

Dari keje yg leceh..kini menjadi mudah..hahaha, aku bukan pemalas taw nak type2 benda yg sama nie..(adakah ini alasan yg kukuh??uhuhu)..


Aku nak share ngn korang..bayangkn maklumat yg korng perlukan ada 4-5 pages..waaa, mesti haru jadinye bukan??..silap copy nnt, semua tak menjadi..putus dipertengahan jalan..bakal jadi "giler" n pelajar universiti sakit jiwa..ehehe

Use VLOOKUP to search through columns of data

To start, download the Excel 2002 sample file: Lookup Function Sample Data. The file uses fictitious data that demonstrates my friend's problem. The file contains two worksheets: Page Views and Pages. The Page Views sheet contains a set of IDs that uniquely identify each site page, plus information on the number of hits each page received during September 2002. The Pages worksheet contains the page IDs and the names of the pages that correspond to each ID.

The page IDs appear in both worksheets because the source database uses a normalized data structure. In that structure, the IDs enable users to find the data for a given page. For a gentle introduction to normalized data structures, see Design Access databases with normal forms and Excel.

Because the data resides in columns, we'll use the VLOOKUP function to enter a page ID on the first worksheet and return the corresponding page name from the second worksheet. Follow these steps:

1. In the Page Views worksheet, click cell E3 and type VLOOKUP.
2. In cell E4, type Result.
3. Click cell F4 and type this formula in either the cell or the formula bar:

   =VLOOKUP(F3,Pages!A2:B39,2,False)

   Note #N/A appears in cell F4 because the function expects to find a value in cell F3, but that cell is empty. You'll add a value to cell F3 in the next step. For more information about fixing #N/A errors, see Correct a #N/A error.

4. Copy the value from cell A4 into cell F3, and then press ENTER. Home Page appears in cell F4.
5. Repeat steps 3 and 4 using the value in cell A5. Comics & Humor appears in cell F4.

Without having to navigate to the second worksheet, you found out which pages receive the majority of visits from site users. That's the value of the lookup functions. You can use them to find records from large data sets with less time and effort.

Understanding the parts of the function

The function that you used in the previous section performed several discrete actions. The following figure describes each action:

The following table lists and describes the arguments that you use with the function. As needed, the information explains how to fix #VALUE and #REF errors that may crop up when you use the functions. You need to know this information to use the function successfully. The HLOOKUP function uses the same syntax and arguments.

Part	Required?	Purpose
=VLOOKUP() =HLOOKUP()	Yes	Function name. Like all functions in Excel, you precede the name with an equal sign (=) and place the required information (or, in geek terms, the arguments) in parentheses after the function name. In this case, you use commas to separate all parameters or arguments.
F3	Yes	Your search term: the word or value that you want to find. In this case, the search term is the value that you enter into cell F3. You could also embed one of the page ID numbers directly into the function. Excel Help calls this part of the function the lookup_value. If you don't specify a search value, or you reference a blank cell, Excel displays the #N/A error message.
Pages!A2:B39	Yes	The range of cells that you want to search. In this case, the cells reside on another worksheet, so the worksheet name (Pages) precedes the range values (A2:B39). The exclamation point (!) separates the sheet reference from the cell reference. If you only want to search through a range residing on the same page as the function, remove the sheet name and exclamation point. You can also use a named range in this part of the function. For example, if you assigned the name "Data" to a range of cells on the Pages worksheet, you could use 'Pages'!Data. Excel Help calls this part of the function the table_array value. If you use a range lookup value of TRUE, then you must sort the values in the first column of your table_array argument in ascending order. If you don't, the function cannot return accurate results.
2	Yes	The column in your defined range of cells that contains the values you want to find. For example, column B in the Pages worksheet contains the page names that you want to find. Since B is the second column in the defined range of cells (A2:B39), the function uses 2. If your defined range included a third column, and the values you wanted to find resided in that column, you would use 3, and so on. Remember that the column's physical position in the worksheet does not matter. If your cell range starts at column R and ends at column T, you use 1 to refer to column R, 2 to refer to column S, and so on. Excel Help calls this part of the function the col_index_num value. If you use the HLOOKUP function, Excel Help calls this part the row_index_num value, and you follow the same guidelines. Note If you use the wrong value in this argument, Excel displays an error message. You can make either of these errors: If the value is less than 1, Excel displays #VALUE!. To fix the problem, enter a value of 1 or greater. For more information about #VALUE! errors see Correct a #VALUE! error. If the value exceeds the number of columns in the cell range, Excel displays #REF! because the formula can't reference the specified number of columns. For more information about fixing #REF errors, see Correct a #REF! error.
False	Optional	Exact match. If you use FALSE, VLOOKUP returns an exact match. If Excel cannot find an exact match, it displays the #N/A error message. For more information about fixing #N/A errors, see Correct a #N/A error. If you set the value to TRUE or leave it blank, VLOOKUP returns the closest match to your search term. If you set the value to TRUE, you must sort the values in the first column of your table array in ascending order. Excel Help calls this part of the function the range_lookup value.

General guidelines for using the VLOOKUP function

Keep these rules in mind as you use the VLOOKUP function:

• If you want the function to return exact matches, you must sort the values in your table array in ascending order or the function will fail.
• The function starts searching at the top left of the cell range that you define, and it searches columns to the right of your starting point.
• You must always separate the arguments with comma.

Use the HLOOKUP function to search through rows of data

The steps in the previous section used the VLOOKUP function because the data resided in columns. The steps in this section explain how to use the HLOOKUP function to find data in one or more rows.

1. In the Pages worksheet, copy the data in the cell range A2 to B39.
2. Scroll to the top of the worksheet, right-click cell D2, and then click Paste Special.
3. In the Paste Special dialog box, select Transpose, and then click OK. Excel pastes the data into two rows starting at cell D2 and ending at cell AO3.
4. In the Page Views worksheet, type HLOOKUP in cell E6, type Result in cell E7, and then enter this formula into cell F7:

   =HLOOKUP(F6,Pages!D2:AO3,2,FALSE)

5. In cell F6, enter the ID from cell A4 and then press ENTER. Home Page appears in cell F6. You get the same type of result, but you searched through a set of rows instead of columns.

The HLOOKUP function uses the same arguments as the VLOOKUP function. However, instead of declaring the column that contains the values you want to find, you declare the row.

Next, let's look at an important principle that applies to both functions. Go to the Pages worksheet and follow these steps:

1. In cells D4 through M4, type anything that comes to mind. You can type anything you want, just add some text or numbers to those cells.
2. On the Page Views worksheet, alter the HLOOKUP formula so it reads as follows:

   =HLOOKUP(F6,Pages!D2:AO4,3,FALSE)

When you finish changing the formula, the value you entered in cell D4 appears. Here's the principle to keep in mind: The value that you want to find does not have to reside in a cell next to your match value. It can reside in any number of columns to the right of your match value, or in any number of rows below your match value. Just make sure that you extend your table_array and col_index_num or row_index_num arguments so that they encompass the values that you want to find.

General guidelines for using the HLOOKUP function

Keep these rules in mind as you use the HLOOKUP function:

• The function starts searching at the top left of the cell range that you define, and it searches the rows below and to the right of your starting point.
• You must always separate the arguments with commas.
• If you want the function to return exact matches, you must sort the values in your data in ascending order. Yes, you can sort horizontally. To do so, follow these steps:
  1. In the Pages worksheet, click cell D2.
  2. On the Data menu, click Sort.
  3. In the Sort dialog box, click Options.
  4. In the Sort Options dialog box, click Sort left to right, and then click OK.
  5. In the Sort dialog box, click OK to sort the data.

In the next Power User column

The next Power User column, More ways to use HLookup and VLookup functions, explains how to:

• Use ToolTips to write functions.
• Use a mix of absolute and relative cell references to return multiple records.
• Debug your functions.
• Use the Lookup Wizard. The wizard automates the process of finding data, but it uses the INDEX and MATCH functions instead of the HLOOKUP and VLOOKUP functions.