Monday, January 9, 2012

Looking Up a Quantity Discount In a Distant Excel Table With VLOOKUP

This is one of the following two articles on VLOOKUP in Excel

VLOOKUP - Just Like Looking Up a Number in a Telephone Book

VLOOKUP To Look Up a Discount in a Distant Database

 

Looking Up a Quantity

Discount In a Distant Excel

Table With VLOOKUP

More often than not, VLOOKUP is used to look up values in a distant Excel table.

For example, a frequent application of VLOOKUP is to obtain a quantity discount from a table located on a distant part of the same spreadsheet. This brief training session will show exactly how to do that. Directly below is a completed example of using VLOOKUP to locate a quantity discount from a distant table:



In this case, VLOOKUP correctly pulls up a quantity discount of 10% for a quantity ordered of 1035. The quantity of 1035 ordered was input by the user.

3 equivalent VLOOKUP commands are shown in the spreadsheet. Each produced the correct Quantity Discount of 10% for a Quantity Ordered of 1035 units. The difference between each of the 3 VLOOKUP commands is the way that the location of the table was specified. 2 of these methods will work in every situation. One of the methods will not work in every situation and should not be used.

To understand how this VLOOKUP function worked, let's first review the inputs for VLOOKUP:


VLOOKUP Inputs

The VLOOKUP function has 4 inputs and follows this format:

VLOOKUP ( Lookup_value, Table_array, Col_Index_num, Range_lookup )

Lookup_value: This is a user-input value for Quantity Ordered and is what will be initially looked up the 1st (leftmost) column of the distant table.

Col_Index_num: Indicates the column number (2) in which VLOOKUP will seek the Quantity Discount that is associated with the user-input Quantity Ordered.

Range_lookup: FALSE if requiring an exact match in 1st column, TRUE or omitted if requiring a similar, but not exact, value in the 1st column. In this case, TRUE will be specified because the quantity ordered might not match the exact increments in the Quantity Ordered column.

Table_array: Location of the distant table is between cells A11 and B17. Table_array location can be specified in 3 ways. These 3 methods are illustrated in the spreadsheet with blue highlighting. They are as follows:

Absolute Addresses have $ placed in front of each row letter and column number. This method will work every time.

Named Ranges will work every time and are created in Excel 2010 by:

1) Highlighting all cells to be included in the range,

2) Formulas / Define Name and then type in the new name of the cell range.

Relative Addresses list an address on the spreadsheet only relative to the current cell. Any changes to the spreadsheet which shift the location of the current cell could cause a Relative Address to point to an incorrect location. This method will not always work.

Tip Of The Day – Always Specify the Table_array location With An Absolute Address or a Named Range. Don’t Use a Relative Address. An Absolute Address or a Named Range will ensure that the location for Table_array will be correct every time. A Relative Address will produce an incorrect result as soon you move anything around on your spreadsheet.

Note that when only a similar match is needed and Range_lookup is therefore set to "TRUE" (the case here), VLOOKUP will select the first lower Quantity Discount if the Quantity Ordered is in between increments listed on the table.

The Quantity Ordered in this case was 1035. Since there is no specific Quantity Discount for the purchase of exactly 1035 units, VLOOKUP matched the 1035 units purchased with the next lower listed increment of Quantity Ordered, which is 1000. The Quantity Discount of 10% is the correct discount for any Quantity Ordered between 1000 and 2000.

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at 4 comments:
Labels:

Wednesday, January 4, 2012

VLOOKUP - Just Like looking a Number Up In a Telephone Book

This is one of the following two articles on VLOOKUP in Excel

VLOOKUP - Just Like Looking Up a Number in a Telephone Book

VLOOKUP To Look Up a Discount in a Distant Database

 

VLOOKUP - Just Like

Looking a Number Up In a

Telephone Book

Using VLOOKUP is just like looking a number up in a telephone book. You first find the name and then find the telephone number on the same row as the name, but in a column to the right of the name.

First, a Few Basics About VLOOKUP

VLOOKUP works inside an Excel table. You specify a value occurring in a cell in the leftmost column (in this example, a person's name in the left column of a telephone book page) and VLOOKUP finds an associated piece of data to the right of that value (in this case, the telephone number directly to the right of that name).
The VLOOKUP function has 4 inputs and follows this format:

VLOOKUP ( Lookup_value, Table_array, Col_Index_num, Range_lookup )


Each of these 4 inputs can be explained as follows:


Lookup_value: The value to be found in the 1st (leftmost) column of the table. In the telephone look-up example below, the Lookup_value would reference the name to be initially looked up. As soon as you open up the telephone book, you look up the name first. That is the Lookup_value for our example below.

Table_array: Location of the table.

Col_Index_num: The column number of the table from which VLOOKUP will return the value. In the telephone look-up example below, Col_Index_num would be the number of the column which holds the telephone number that VLOOKUP is locating. Since our data table only has 2 columns of data, the telephone number would be in the 2nd (rightmost) column.

Range_lookup: FALSE if requiring an exact match in 1st column, TRUE or omitted if requiring a similar value in the 1st column. When looking up a specific item, such as a telephone number, an exact match is usually required and the Range_lookup will be set to FALSE in this case.

Let's go right into our example which shows VLOOKUP being used to look up a telephone number, just as you would do in a telephone book.


Using VLOOKUP to Locate a Telephone Number

Below is the spreadsheet representing a page of a telephone book. VLOOKUP looks up the telephone number in the same way that you would: locate the name and then look to the correct column on the right to get the telephone number.

In this case, VLOOKUP is locating the telephone number of Andre Johnson. The two equivalent VLOOKUP commands used below produce the same correct result (760-4829). The only difference between the two commands is how each looks up Andre Johnson's name.

The first VLOOKUP command looks up cell A4, which contains Andre Johnson's name. The second VLOOKUP command searches for "Andre Johnson" in the first (leftmost) column of the table.

Using VLOOKUP in Excel Is Just

Like Looking Up a Telephone

Number In a Phone Book


Note in the Telephone Book Lookup example above that the two VLOOKUP commands located the same telephone number (760-4829):


=VLOOKUP(A4,A2:B5,2,FALSE)

=VLOOKUP("Andre Johnson",A2:B5,2,FALSE)

Here is an explanation of the inputs for the VLOOKUP command as they pertain to this example:

Lookup_value: The value to be found in the 1st (leftmost) column of the table. We are looking up the value “Andre Johnson” in cell A4.

Table_array: Location of the table. The table is located between cells A2 and B5.

Col_Index_num: The column number of the table from which VLOOKUP will return the value. VLOOKUP will locate the telephone number in column 2 directly to the right of “Andre Johnson” in cell A4.

Range_lookup: FALSE if requiring an exact match in 1st column, TRUE or omitted if requiring a similar value in the 1st column. We do require an exact match of “Andre Johnson” in the 1st column so we set Range_lookup to FALSE.

VLOOKUP works just like looking a phone number in the phone book. Locate the name first, and then the phone number to the right in a specified column, on the same row as the name.In fact, the telephone lookup is probably the simplest and most intuitive use of VLOOKUP. Let’s provide an example of using VLOOKUP to do exactly that.

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at 2 comments:
Labels: , ,
Subscribe to: Posts (Atom)