+ Reply to Thread
Results 1 to 3 of 3

Lookup Price Based on Quantity Breaks Across Rows

  1. #1
    Registered User
    Join Date
    07-22-2019
    Location
    United States
    MS-Off Ver
    2010
    Posts
    15

    Question Lookup Price Based on Quantity Breaks Across Rows

    Hello. I am trying to compute a total part cost based on various anticipated quantities over five years and the appropriate price
    quantity breaks. My research shows there are a lot of posts using Vlookup, but my data is across the row & not down a column.
    I have three tables. The first is the parts table with various data & including the part quantity price breaks and the associated
    price for each break. The next table is the estimated quantity for each part by year. The final table that I a trying to find an
    appropriate formula for is the cost of the parts for each year based on the quantity breaks, the associated price for that particular
    break and the anticipated quantities for each year.

    I have attached an example spreadsheet & would greatly appreciate anyone's help. Hopefully, this can be accomplished using
    an Excel formula vs. VBA.

    Thank you.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Lookup Price Based on Quantity Breaks Across Rows

    In B32

    =INDEX($G3:$K3,MATCH(B$17,$B3:$F3,1))*B$17

    Copy across

    See changes to Qty breaks in B3:F3

    The results in green show a different tiered rates where with qty of 125, first 100 at rate1 and 25 at Rate2.

  3. #3
    Registered User
    Join Date
    07-22-2019
    Location
    United States
    MS-Off Ver
    2010
    Posts
    15

    Re: Lookup Price Based on Quantity Breaks Across Rows

    This solution worked great. Thank you John.

+ 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. [SOLVED] supplier based on price and quantity
    By The_ShadowWw in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-28-2020, 08:35 AM
  2. [SOLVED] Need cell to lookup price then multiply by quantity
    By CheleC in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-09-2019, 06:09 PM
  3. [SOLVED] Sum quantity and price based on first 2 characters
    By gan_xl in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-05-2018, 06:12 AM
  4. [SOLVED] Setting a selling price based on cost price and order quantity
    By Steven811 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-11-2014, 08:07 AM
  5. Replies: 4
    Last Post: 08-15-2012, 09:49 AM
  6. Searching applicable price based on various quantity
    By Umer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-25-2009, 01:48 PM
  7. Calculate price based on quantity
    By DieselNash in forum Excel General
    Replies: 5
    Last Post: 04-21-2009, 10:51 AM

Tags for this Thread

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