+ Reply to Thread
Results 1 to 8 of 8

Lookup MIN in 3 separate ranges, return col. heading

  1. #1
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,029

    Lookup MIN in 3 separate ranges, return col. heading

    Found formula to find lowest price in separate ranges, need to return column header of the lowest price.
    Worksheet attached.

    BestPriceExcelIndexMatch-1.xlsx

    Many thanks for ideas.

    Pete

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Lookup MIN in 3 separate ranges, return col. heading

    I don't like manual reconfiguring of formulas, so I rewrote your MIN.

    B3:

    =MIN(IF(($J$2:$R$2=B$2)*($I$5:$I$9=$A3)*($J$5:$R$9)<>0,($J$2:$R$2=B$2)*($I$5:$I$9=$A3)*($J$5:$R$9)))

    (array formula)

    D3:

    =LOOKUP(2,1/(($J$2:$R$2=B$2)*OFFSET($J$4:$R$4,MATCH($A3,$I$5:$I$9,0),0)=B3),$J$3:$R$3)

    Copy down and over as needed.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,029

    Re: Lookup MIN in 3 separate ranges, return col. heading

    I plugged in the suggested formulas; B3 returned a - (dash), Could you revisit, or upload a copy of the formula/workbook that isn't misbehaving?

    Many thanks..

    Pete
    Last edited by PeteABC123; 10-15-2014 at 09:45 AM.

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Lookup MIN in 3 separate ranges, return col. heading

    I apologize for not remembering to add:

    The Value you have in B2 is doesn't match the values of J2:R2.

    Change it to "Qty 1" with a space like you have in D2 and F2, instead of "Qty1"

    Then the formula will work.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,029

    Re: Lookup MIN in 3 separate ranges, return col. heading

    You're my new best friend! And a daysaver too.

    Thanks so much.

    Pete

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Lookup MIN in 3 separate ranges, return col. heading

    No problem. Always like a good challenge.

    If that takes care of everything, you should mark the thread as Solved, under Thread Tools at the top.

  7. #7
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Lookup MIN in 3 separate ranges, return col. heading

    Life's a spreadsheet, Excel!
    Say thanks, Click *

  8. #8
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,029

    Re: Lookup MIN in 3 separate ranges, return col. heading

    Ace_XL, that works beautifully as well! Thank you for the work you did on it.

    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. [SOLVED] Whick lookup needed to return a column heading?
    By Thistledown in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-11-2014, 08:08 AM
  2. Replies: 6
    Last Post: 02-25-2014, 10:49 AM
  3. lookup values in a range,return corresponding column heading
    By aljaffa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2011, 04:31 PM
  4. Replies: 3
    Last Post: 04-27-2010, 01:47 AM
  5. Lookup in various ranges, return maximum value
    By adammj58 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-15-2008, 04:44 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