+ Reply to Thread
Results 1 to 2 of 2

Search for minimum price with dynamic range and return price and product ref

  1. #1
    Registered User
    Join Date
    11-14-2012
    Location
    Spain
    MS-Off Ver
    Excel 2007
    Posts
    34

    Search for minimum price with dynamic range and return price and product ref

    Good afternoon,

    I would like some help with the following Excel problem:

    There's a list of products. In the example worksheet attached there's 3 products: T-shirt 1, t-shirt 2 and t-shirt 3 (however, there could be other products such as Trouser 1). The products have different prices depending on small differences between them (e.g. T-shirt 1 may have the t-shirt logo in the inside either printed or stitched) and I would like to find the minimum price for each product (i.e. For t-shirt 1, for t-shirt 2 and for t-shirt 3).

    The problem I find is that in reality there are many more products, so the range should be dynamic (there may be three t-shirt 1s and 15 t-shirt 4s).

    Additionally, if the price is exactly the same, I would like the summary table to return the t-shirt reference with the lowest minimum purchase requirements.


    Best,

    gjrr4x1
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,513

    Re: Search for minimum price with dynamic range and return price and product ref

    You can put this array* formula in H5:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and this array* formula in I5:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and then copy the formulae down.

    * An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this correctly, then Excel will wrap curly braces { } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to edit the formula subsequently then you will need to use CSE again. You can copy/paste an array formula in the normal way(s).

    Hope this helps.

    Pete

+ 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. Replies: 4
    Last Post: 08-15-2012, 09:49 AM
  3. Help choosing minimum price if in stock
    By debbiet@projectors in forum Excel General
    Replies: 14
    Last Post: 03-12-2012, 10:57 AM
  4. Updating excel range with dynamic price data
    By Matt1234au in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-06-2009, 09:13 AM
  5. How do I set a price minimum in a spreadsheet?
    By phahn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-09-2005, 02:10 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