Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
Join Date
7th September 2006
Posts
19

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.

Excel Video Tutorials / Excel Dashboards Reports

2. Long Term Member
Join Date
6th May 2005
Posts
1,036

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. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

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. I agreed to these rules
Join Date
7th September 2006
Posts
19

10x tony,
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)
Last edited by John Evans; June 21st, 2007 at 15:48.

Excel Video Tutorials / Excel Dashboards Reports

5. Long Term Member
Join Date
6th May 2005
Posts
1,036

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

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

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