+ Reply to Thread
Results 1 to 7 of 7

Formula for getting price from item listing when there is more than one price level column

  1. #1
    Registered User
    Join Date
    11-18-2014
    Location
    Perth, Australia
    MS-Off Ver
    Microsoft Office Home and Student 2010
    Posts
    4

    Formula for getting price from item listing when there is more than one price level column

    Hi All,

    I need help please with the attached workbook.

    In cell D8 On the 'QUOTE' tab I want to be able to select the required price level from the drop down list and then have a formula in column H that will lookup the appropriate price from the relevant price level column on the 'ITEMS' tab based on the price level selected in cell D8.

    Any advice greatly appreciated.

    Thanks in advance.

    Mel
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,635

    Re: Formula for getting price from item listing when there is more than one price level co

    replace formula in column H 12 with
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and fill down

    the lookup section will return
    1 for L0
    2 for L4
    3 for L7

    thereby increase your vlookup column index number accordingly
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,235

    Re: Formula for getting price from item listing when there is more than one price level co

    Please find file attach, using array formula, means when ENTER you need to confirm press CTRL-SHIFT-ENTER button together, and copied down.
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,760

    Re: Formula for getting price from item listing when there is more than one price level co

    In H12 =IF($A12>0,VLOOKUP($A12,ITEMS!$A$2:$L$388,9+MATCH($D$8,ITEMS!$J$1:$L$1,0),FALSE), " ") and copy down

  5. #5
    Registered User
    Join Date
    11-18-2014
    Location
    Perth, Australia
    MS-Off Ver
    Microsoft Office Home and Student 2010
    Posts
    4

    Re: Formula for getting price from item listing when there is more than one price level co

    Thanks for all your replies. I will try these suggestions and let you know how I go.

  6. #6
    Registered User
    Join Date
    11-18-2014
    Location
    Perth, Australia
    MS-Off Ver
    Microsoft Office Home and Student 2010
    Posts
    4

    Re: Formula for getting price from item listing when there is more than one price level co

    Quote Originally Posted by alansidman View Post
    In H12 =IF($A12>0,VLOOKUP($A12,ITEMS!$A$2:$L$388,9+MATCH($D$8,ITEMS!$J$1:$L$1,0),FALSE), " ") and copy down
    I tried this solution and am happy to report that it is working as expected.

    Many thanks Alan.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,760

    Re: Formula for getting price from item listing when there is more than one price level co

    You are welcome. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 01-03-2013, 12:32 AM
  2. [SOLVED] Compare 1 price against multiple prices and change the price according to a formula
    By CharlieAziz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-16-2012, 11:05 AM
  3. Replies: 4
    Last Post: 08-15-2012, 09:49 AM
  4. Replies: 3
    Last Post: 07-17-2012, 03:34 AM
  5. [SOLVED] create a formula for price * discount* tax =final price
    By anton in forum Excel General
    Replies: 6
    Last Post: 10-12-2005, 03:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1