Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup function (Excel 2000)

    Would someone mind looking at the attached spreadsheet and telling me where I'm going wrong? I've spent WAY too much time trying to figure the problem out today and I still don't have the answer. This is a sales order spreadsheet. The relevant inputs are 1) pricing level (AR2), 2) quantity, 3) unit of order (cases or pallets) and 4) and product (one of four choices based on package size and labels). The formula I'm having problems with is in the Unit Price column. It uses some tables on the Pricing Table tab to determine the proper unit price which is then multiplied by the quantity to determine the total price for the line item. Everything seems to work fine for the first three selections from the drop down menu in the pricing level box. However, when I choose the fourth option (12 pallets), the Unit Price formula returns #N/A, which apparently means it can't locate that value in the lookup table. However, I have verified that the value is indeed there. What am I missing? I protected the worksheet without a password in case you need to look at any of the locked cells. Many thanks.

    Phil
    Attached Files Attached Files

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Vlookup function (Excel 2000)

    Further to Legare's post...

    If you put the "Pricing Table A16:B19" in ascending order then your formula works.
    As follows:

    12 Pallets 5
    3 Pallets 2
    3-6 Pallets 3
    6-12 Pallets 4

    Regards,

    Jim Cone
    San Francisco, CA

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup function (Excel 2000)

    Legare and Jim, thanks for your help.

    Phil

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup function (Excel 2000)

    Changing your formula to the below seems to work:

    <pre>=VLOOKUP(F24&VLOOKUP(L24,'Pricing Table'!A$11:B$14,2,FALSE),'Pricing Table'!A$5:E$8,VLOOKUP(AR$2,'Pricing Table'!A$16:B$19,2,FALSE),FALSE)
    </pre>


    I'm not sure what the LOOKUP function is doing, but it appears that it is doing an approximate match that does not work on the last entry in the tables.

    Edited by Legare Coleman to change that last TRUE to FALSE so that the original VLOOKUP does and exact match.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •