Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Dual Headings Match

  1. #1
    Join Date
    7th September 2006
    Posts
    19

    Dual Headings Match

    TAKING A BIT FURTHER THE POST http://www.ozgrid.com/forum/showthread.php?t=69644

    Here's my problem: I am trying to find the MIN value in a column with dual headings and match the corresponding product which makes my formula with three criteria.


    Please see the attachment to see the structure of the tables and further explanation.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    6th May 2005
    Posts
    1,036

    Re: Dual Headings Match

    Hi


    Try

    =INDEX(B2:E2,0,MAX(IF(MIN(IF(B3:E3=I21,OFFSET(B3,MATCH(H21,A4:A6,0),0,1,4),MAX(B4:E6)))=OFFSET(B3,MATCH(H21,A4:A6,0),0,1,4),COLUMN(B5:E5)-1,"")))

    array entered.


    Tony

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Dual Headings Match

    Hi,

    =INDEX(A2:E2,MAX(IF(B4:E6=MIN(IF(A4:A6=H21,IF(B3:E3=I21,B4:E6))),COLUMN(B4:E6))))

    To Enter the array formula hold down Ctrl and Shift while pushing Enter.

    HTH

  4. #4
    Join Date
    7th September 2006
    Posts
    19

    Re: Dual Headings Match

    10x tony,
    your formula works fine
    I am just trying to figure it out how to make the absolute referencing($) to drag it down without messing-up the arrays Krishnakumar solution is much more simpler from this aspect (INDEX($A$2:$E$2,MAX(IF($B$4:$E$16=MIN(IF($A$4:$A$16=H23,IF($B$3:$E$3=I23,$B$4:$E$16))),COLUMN($B$4:$E$16)))) but it does not work corectly (see the attachment)
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by John Evans; June 21st, 2007 at 15:48.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    6th May 2005
    Posts
    1,036

    Re: Dual Headings Match

    Hi

    Here it is expanded for your new sample, and absoluted.

    =INDEX($B$2:$E$2,0,MAX(IF(MIN(IF($B$3:$E$3=$I21, OFFSET($B$3,MATCH($H21,$A$4:$A$16,0),0,1,4),MAX($B$4:$E$16)))=OFFSET($B$3,MATCH($H21,$A$4:$A$16,0),0,1,4),COLUMN($B$5:$E$5)-1,"")))

    Tony

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 3
    Last Post: August 30th, 2007, 09:37
  2. Extract Column From Table With Dual Headings
    By brook1 in forum Excel General
    Replies: 3
    Last Post: November 20th, 2006, 06:06
  3. Dual load possible?
    By Aussie in forum Excel General
    Replies: 1
    Last Post: November 6th, 2004, 21:16
  4. Dual List function
    By Zaunaa in forum Excel General
    Replies: 8
    Last Post: October 15th, 2004, 18:59
  5. Dual Y-axes
    By danielobie in forum Excel General
    Replies: 2
    Last Post: May 13th, 2003, 00:16

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
  •  
porno