Results 1 to 4 of 4

Find value in third column based on values in two cells from two columns

Threaded View

  1. #1
    Registered User
    Join Date
    04-25-2014
    Location
    Riverside, CA
    MS-Off Ver
    Excel 2016
    Posts
    6

    Find value in third column based on values in two cells from two columns

    Hi -

    So I have been struggling with this for a good minute.

    I want to find the maximum value in one column based on whether a cell value is within a range of another column's values which is based on another cell value in another column.

    For example, I have a pricing list consisting of products, product codes, order quantities and pricing. I'm trying to find what the new price would be for a product code based on the quantities ordered for my analysis.

    So, for example, if a customer ordered 525 quantities of TOP2505, this would result in the 500 Order Quantity Pricing range and would result in the pricing of $1.58. I've attached a workbook example.

    However, the pricing list that I have does not have ranges built into it--it is only a list of order quantities--and there are multiple prices in the list. (I would like to pull the highest price value for that quantity range.)

    I was able to come up with:


    {=MAX(IF(B2:B18=G9,IF(C2:C18>=H9,D2:D18)))}


    But this is bringing me the next price up (qty for 1,000); not the range between 500 and 1,000 (which should be 500).

    What's more, is that this is an array; my actual workbook contains a year's worth of orders/quantities and I can't drag this formula down my list (and you know my list of products isn't limited to tops).

    Does anyone know if there's a way to do this? Or is this just impossible?
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Find Unique Values Based On Multiple Columns
    By aimone111 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-08-2016, 05:22 AM
  2. [SOLVED] Find count of Unique or Duplicate Values based on Concatenated values in 2 columns
    By bdicarlo1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-03-2014, 12:42 AM
  3. Replies: 5
    Last Post: 05-14-2014, 08:35 AM
  4. Count Unique values in Column based on values in other columns
    By dmschave in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 11:06 AM
  5. Replies: 1
    Last Post: 11-16-2013, 05:18 AM
  6. Find duplicate values based on 2 separate columns
    By dreich in forum Excel General
    Replies: 1
    Last Post: 01-30-2013, 01:41 AM
  7. Replies: 0
    Last Post: 09-01-2010, 02:47 PM

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