Showing posts with label vlookup. Show all posts
Showing posts with label vlookup. Show all posts

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

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

Thursday, January 27, 2011

VLOOKUP in Excel To FInd List Matches in 2 Steps and Then Remove Them

Using VLOOKUP In Excel

To Find List Matches

In 2 Steps

and Then Remove Them



If you are an Internet marketer, you already know the importance of list management skills. Nothing beats Excel as a platform to manipulate the data in your lists.

Here is a quick lesson on how to find matching records in 2 lists in just 2 steps using VLOOKUP. We will then show you how to quickly remove those matches from either of these lists, leaving you with the original list with all records that have no matches in the other list.

Finding Matches in 2 Lists Using VLOOKUP

Step 1 – Arrange the Lists Properly and Perform a VLOOKUP

Here are the 2 lists. Arrange them so that the larger list is on the left:




Now, perform your VLOOKUP. Type the formula shown below into cell D2 and copy it down to cell D9. The results of the VLOOKUP appear in cells D2 to D9. An explanation of this VLOOKUP is provided next.

Click on the Image To See Larger Version

 
Here is an explanation of each argument of the VLOOKUP function in this case:



Column D contains the results of the VLOOKUP. The actual contents of the cells in this column are the VLOOKUP formulas, not the values showing in the cells. We need to work with only the values that appear and not the formulas.


Step 2 - Copy and Sort the Values from VLOOKUP

To do this, copy the contents of the column and then paste only their values using Paste / Paste Special / Values as shown here:

Click on the Image To See Larger Version


Now that we have a column of values and not formulas, we can sort the column with the ordinary Sort function under the Data tab as follows:

Click on the Image To See Larger Version


The Matches in Both Lists

Here is the result of the Sort. Highlighted in yellow are the matches from both lists:



Removing the Matches From List 1.

Paste the matches at the bottom of List 1:




Sort List 1 to get this result:



Use an If-Then-Else statement to print any cells that do not have matches above or below them. If a cell above or below matches that cell, only a blank space (“ “) will be printed. The formula shown in cell Q4 can be copied in cells Q2 to Q13 to get the following result:

Click On Image To See Larger Version


The contents of the cells in column Q are actually formulas, not the numbers that appear in the cells that are the result of the calculation. We need to copy only those numerical values into a column so we can perform data operations on them.

Do this the same way it was done above. Copy the contents of cells Q2 to Q13 and paste them into a new column using Paste Special / Values to get the following result:

Click On Image To See Larger Version


Sorting these will give you the final List 1 (highlighted in yellow below) with all matches from List 2 removed:

Click On Image To See Larger Version


You could easily run the same removal procedure on List 2 to remove the matches from that list as well. If you did that, you would have 2 lists that had no matching records.




If you would like to create a link to this blog article, here is the link to copy for your convenience: Using VLOOKUP In Excel To Find and Remove Matches in 2 Lists

Using VLOOKUP In Excel To Remove Matches in 2 Lists
Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...
Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic