VLOOKUP (excel tutorial) - cerita Menarik

cerita Menarik

Menampilkan Cerita yang menarik

test banner

Post Top Ad

Responsive Ads Here

3.31.2010

VLOOKUP (excel tutorial)


= = VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Let’s say you sell books for a living. You want to keep an inventory of books you are selling, and you charge different amounts based on the type of book; whether the book is paperback, hard cover, or a best seller. You charge $10 for a paperback book, $25 for a hard cover book, and $30 for a best seller. Based on the type of book, you want to display how much the book is. For this, we use a formula called VLOOKUP.

This is our spreadsheet. Notice we have a table showing each of the types of books, and how much each one sells for. Then we have our inventory list, with 6 books listed. We want a simple formula to fill in the price for each book.

We want to enter this formula in the first cell in the “Price” column. As entered above, we want to enter “=VLOOKUP(“ into the cell. The formula pops up VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). There are 4 components to the VLOOKUP function:

Lookup_value: what we want to search for in the table that lists the different types and price. In this case, we want to search for the value listed in the “Type” column. In this example, the first type of book is listed in cell C7. The value searched is always searched in the first column of the table

Table_array: what table we want to search in. For this example, the table is on the top, starting in cell A1 and ending in cell B3. Our table array is $A$1 to $B$3, so we want this to be $A$1:$B$3. Notice the $ are added because the table is in an absolute position.

Col_index_num: the column of the table we’re searching in that will be displayed in the cell. For example, entering “2” here would have the formula return the value of the 2nd column. In this case, we want the formula to return the price of the book, which is in the 2nd column. So, 2 is the value we want here.

(OPTIONAL) Range_lookup: lets the function know whether you want to return an exact match or a similar match. FALSE is entered if an exact match is needed, TRUE is entered if an exact match is not needed. If nothing is entered, the value is TRUE. If TRUE is the value, and VLOOKUP cannot find the search in the table, it will look for the largest value that is smaller than the value searched for (for numerical searches). However, using TRUE also assumes that the table is alphabetically or numerically sorted. Our table is not, because Paperback, hardcover, bestseller is not in alphabetical order. Because of this, for this example, we must use FALSE.

So, we have our formula:
=VLOOKUP(C7, $A$1:$B$3, 2, FALSE)

And there you have it. We enter the formula, and drag it down the column. All the values are entered into the “Price” column. The values are not prices, but that can be fixed easily by formatting the cells into currency notation.


No comments:

Post a Comment

Post Bottom Ad

Responsive Ads Here