Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Dual Headings Match

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      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
      Kris

      ExcelFox

      Comment


      • #4
        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
        Last edited by John Evans; June 21st, 2007, 15:48.

        Comment


        • #5
          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

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X