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
Labels:

4 comments:

  1. AnonymousMay 12, 2012 at 8:50 AM

    good, I like this.....but can you help me?
    I have problems in my cases excel:
    examples:
    (12)= writed (3)
    (25)= writed (7)
    (10)= writed (1)
    (89)= writed (8)
    (22)= writed (4)
    (99)= writed (9)
    etc.... how to make a program in excel?
    and else then
    i want to loop a number for examples as bellow:
    0 1 2 3 4 5 6 7 8 9 back to again o 1 2...
    so:
    0 1 2 3 4 5 6 7 8 9 0 1 2 3 4....how to make program in excel

    thank you master

    ReplyDelete
  2. AnonymousJune 26, 2012 at 12:42 PM

    I have two seperate sessions of excel Open. When i do the vlook up function table. I click in table array to fill it in but when i click on the second session to highlight what i want for the table array it does not recognize it and wont add it to the table array box? Any clue why? Or how to fix it?

    ReplyDelete
  3. vé máy bay từ canada về việt namSeptember 14, 2021 at 8:06 PM

    Đặt vé tại phòng vé Aivivu, tham khảo

    vé máy bay đi Mỹ khứ hồi

    chuyến bay cứu trợ mỹ về việt nam

    vé máy bay từ frankfurt đi hà nội

    chuyến bay thương mại từ nhật về việt nam

    mua ve may bay tu han quoc ve viet nam

    vé máy bay từ canada về việt nam bao nhiêu tiền

    khách sạn cách ly hà nội

    ve may bay chuyen gia nuoc ngoai

    ReplyDelete
  4. shopifyMarch 14, 2022 at 5:20 AM

    team communication guide You need an effective team communication guide because your team can only perform towards a common goal when everyone is clear and focused on the same goal.

    ReplyDelete

Subscribe to: Post Comments (Atom)