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.

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.

Pivot Tables - One Easy Step That Will Double the Effectiveness of All of Your Pivot Tables!

If you have never added a Pivot Chart to your Pivot Tables, you will be very pleasantly surprised at how much Pivot Charts instantly increase clarity, and how easy they are to add.

Adding a Pivot Chart to a Pivot Table

The easiest way to add a Pivot Table is to do it when creating the Pivot Table. It just takes one additional click as follows:

Insert / Pivot Table / Pivot Chart

That’s it. You now create your Pivot Table in the normal fashion, and the Pivot Chart is automatically generated. Any inputs you add or changes you perform to the Pivot Table are immediately reflected in the accompanying Pivot Chart.

The graphs on the Pivot Chart will convey your data’s meaning and secrets much faster than the numbers of the Pivot Table. The demonstration below will make you a believer in creating an accompanying Pivot Chart with each Pivot Table:

The Pivot Chart vs. The Pivot Table

We will use the same small raw data set that we used in yesterday’s Session 3:

Arranging the raw data’s column headers in Pivot Table rows as follows:

produces the following Pivot Table:

We are comparing one salesperson’s performance on each product with another sales-person’s. The Pivot Table results indicate that one salesperson is outperforming the other.

The extent to which that one salesperson outperforms the other becomes much more apparent when the same data is displayed on the accompanying Pivot Chart:



Now, suppose we rearrange the data on the Pivot Table by shifting the row headers around as follows:

The Pivot Table is rearranged as follows:

We are trying to evaluate each salesperson’s performance on each product over time. The numbers on the Pivot Table don’t provide much clarity. However, the trends become crystal clear immediately when viewing the accompanying Pivot Chart:

Tip of the Day

Whenever possible, add a Pivot Chart to your Pivot Table. Once the chart is set up, rearrange the data in as many ways as possible. You will discover new insights into your data that you never would have by looking only at the numbers. It’s well worth the one additional click it takes to create an accompanying Pivot Chart !

Pivot Tables - How To Set Up a Pivot Table Query Correctly Every Time

Pivot tables allow you to quickly pull useful information out of large amounts of raw data. The most common use of pivot tables is to summarize data by all available categories and subcategories within the raw data.

The incredible versatility of pivot tables allows you to expand and collapse all levels of data and instantly reshuffle all categories and subcategories in order to obtain just the right information and insights.

All of that power and versatility can leave a person new to pivot tables wondering where to start. Fortunately, pivot tables are quite a bit more intuitive than they initially appear. The most important rule for using them effectively is the following:

Correctly word your question about the information you seek and you will instantly know how to configure your pivot table.

Here is a good framework for wording your question:

For each ___________,
For each ___________,
Compare ___________.

For example, if you wanted to compare the results of each salesperson on each product line during each quarter, you would word your query using the above framework as follows:

"For each quarter, for each product, compare each salesperson's sales."

Wording your query this way tells you exactly how to configure your pivot table correctly. Let’s see how to run the above query on the following small set of raw data:



Pivot Table Set Up

Setting up a pivot table takes just a few seconds. You locate Pivot Tables in Excel 2010 with:

Insert / Pivot Table

You are asked to highlight your raw data table. Be sure to include the table’s column or row headings when you highlight the raw data. You are then asked to specify the cell that will be the upper left corner of the output. That’s all there is to it. You’re now ready to make a Pivot Table.

The Pivot Table dialogue box then opens up with a list of all row or column headings from your table of raw data as follows:



Each heading listed here will control either numeric or non-numeric data. The following headers control non-numeric data: Salesperson, Quarter, and Product. The Sales header controls the only numeric data column.

Each header whose checkbox you click will have its information included in the Pivot Table. Pivot Tables are used to analyze numeric data. The Sales header should therefore be clicked.

Now you are ready to click on any non-numeric headers whose information you wish to include in the Pivot Table. When you click on each non-numeric header, you can then drag it down to become a Row Label or a Column Label.

Here is today's  2nd biggest tip regarding building Pivot Tables:

Set up all of your non-numeric headers as row labels, not column labels.

Your Pivot Table will be much easier to understand and interpret if all categories and subcategories of data are arranged stacked up-and-down (as rows) instead of side-by-side (as columns).

How To Arrange the Non-numeric Headers in the Pivot Table

Your arrangement of the non-numeric headers in the Pivot Table will be determined by how you word your query with the following framework:

For each (Top Header),
For each (Next Lower Header),
Compare (Next Lower Header).

For example, let's create a Pivot Table for the following query that we were discussing above:

For each Quarter,
For each Product,
Compare each Salesperson’s Sales.

You can see below that this query is created by dragging headers Quarter, Product, Salesperson into the Row Labels box in that order as shown here:



Arranging the headers this way creates the following Pivot Table shown below. You can see at the Row Labels Box at the bottom of the Pivot Table dialogue box on the right side.

If you only wanted to run the following query:

For each Quarter,
Compare each Salesperson’s Sales.

You would only click on headers Quarter and Salesperson and then arrange them using the above method (Quarter above Salesperson in the Row Labels Box) as follows:

 to produce the following Pivot Table:

As often happens, getting the right answer depends a lot on asking the right question.

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


Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...

Using If-Then-Else In Excel To Remove Matches in 2 Lists

Using If-Then-Else In Excel

To Remove Matches

in 2 Lists

One thing we Internet marketing managers do is handle lots of data. List management skill is a key requirement for this job. A situation I run into quite often, particularly when I’m doing SEO work, is to locate and remove matches from 1 list that are found in a 2nd list.

For example, I have Lists 1 and 2 as shown below.

Click On Image to See Larger Version

I want to remove any records from List 1 that also appear in List 2, which would leave me with the following List 1:

Click On Image to See Larger Version

The If-Then-Else-Statement

One easy way to do this is to use the If-Then-Else statement. In my own humble opinion, the Else-Then-Else statement is the most useful bit of code available to the Excel modeler. I don’t know that I’ve ever made a detailed Excel model that didn’t make use of If-Then-Else at least once, and usually multiple times. I couldn’t live without If-Then-Else.

There are 2 main steps to removing matches in 1 list that are found in another. They are:

1) Determine what records exist in both lists
2) Remove those records from List 1


Main Step 1 – Find Matches Records That Exist In Both Lists

Here are the two lists once again:

Click On Image to See Larger Version

Here are the matches in each list highlighted with matching colors:

Click on Image To See Larger Version

Remove the duplicates in each list. Select both lists and Data / Remove Duplicates and then check both rows:

Click on Image To See Larger Version

Duplicates in the same column as an original record will be removed:

Click on Image To See Larger Version

Combine the lists. The easiest way is to copy one list and paste it right on top of the other, like so:

Click on Image To See Larger Version

Now, sort the list by Data / Sort as follows:

Click on Image To See Larger Version

The result of the sort looks like this:

Click on Image To See Larger Version

Use the If-Then-Else statement to place a “1” next to one record of each matching pair. Cell Q11 has the If-Then-Else formula =if(P11=P12,1,” ”)..

This means: If Cell P11 = Cell P2, Then Output a 1, Else don’t output anything into this cell. This If-Then-Else is shown below:

Click On Image To See Larger Version

The cells that contains the “1” and “ “ actually contain formulas. We need to replace the formulas with the cells with the contents (the “1”’s and “ “). We do that by copy all of these cells as follows:

Click on Image To See Larger Version

and then pasting them back on top of each other using Paste Special / Values as follows:

Click on Image To See Larger Version

The end result looks the same as it did before:

Click on Image To See Larger Version

Now we are trying to isolate the matches. These would be cells that have a 1 next to them. Highlight all the cells and Data / Sort as follows:

Click on Image To See Larger Version

We are sorting by the columns with the 1’s in it, as follows:

Click on Image To See Larger Version

The sort moves all records having a 1 in the right cell are moved to the top as follows:

Click on Image To See Larger Version

Main Step 2 - Remove the Matches From the Lists
Next copy the matches over, as follows:

Click on Image To See Larger Version

Now, paste these matches at the bottom of de-duped List 1:

Click on Image To See Larger Version

Sort List1 again, to get the following result:

Click on Image To See Larger Version

Use another If-Then-Else statement to place a 1 next to any match. This statement places a 1 next to a cell if it equals the cell over it or the cell under it, as follows:

Click on Image To See Larger Version

Perform the Copy / Paste Special operation again to convert the contents cells in the right column from formulas (If-Then-Else statements) to actual numbers.

Click on Image To See Larger Version

Sorting these 2 columns again to brings matching records (records with a 1 in the right column) to the top, as follows:

Click on Image To See Larger Version

Here is the result of this sort:

Click on Image To See Larger Version

Delete those records on top and here is the result and final objective: List1 with all records removed that have no matches in List2:

Click On Image To See Larger Version

You could easily run the same removal procedure on List2 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 If-Then-Else 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...