Problem with Index-Match function

Hello and welcome to the forum. Your attachment didn't work.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

Sorry, I wasn't aware of how to properly attach the file. Since I was using an image, I thought insert image would work.

Try this:

=INDEX(1:1,MAX(IF(B2:E11=K7,COLUMN(B2:E11)))) Ctrl Shift Enter

Whoops, the formula in post #4 should have used B2:G11 and not B2:E11.

If there were two cells with the same maximum value, how do I modify this formula to return the column header of the second of those two values?

The formula already does this. It pulls the right-most column that has the max number in it.

And if I wanted to return the row along which the maximum value occurred, how do I modify the formula?

Assuming that you want to return the Day # from column A, not the actual row #, try this:

=INDEX(A:A,MAX(IF(B2:G11=K6,ROW(B2:G11)))) Ctrl Shift Enter

Note that the formula (that returns the Day #) from post #6 will return the Day # of the furthest down row that has the max number in it.

Originally Posted by 63falcondude

The formula already does this. It pulls the right-most column that has the max number in it.

Right, didn't notice that. What I meant was if I have multiple rows/columns that contain the maximum value, how do I choose to return the column/row heading for the first, second or third instance of that value?

I have a feeling that the requirements are going to continue to change until I can no longer help but I'll hang in there for now.

Column Heading:
First Match =IFERROR(INDEX(1:1,SMALL(IF(B2:G11=K7,COLUMN(B2:G11)),1)),"") Ctrl Shift Enter
Second Match =IFERROR(INDEX(1:1,SMALL(IF(B2:G11=K7,COLUMN(B2:G11)),2)),"") Ctrl Shift Enter
Third Match =IFERROR(INDEX(1:1,SMALL(IF(B2:G11=K7,COLUMN(B2:G11)),3)),"") Ctrl Shift Enter

This goes from left to right.

Row Heading (Day #):
First Match =IFERROR(INDEX(A:A,SMALL(IF(B2:G11=K7,ROW(B2:G11)),1)),"") Ctrl Shift Enter
Second Match =IFERROR(INDEX(A:A,SMALL(IF(B2:G11=K7,ROW(B2:G11)),2)),"") Ctrl Shift Enter
Third Match =IFERROR(INDEX(A:A,SMALL(IF(B2:G11=K7,ROW(B2:G11)),3)),"") Ctrl Shift Enter

This goes from top to bottom.

If the Day #'s are supposed to be correlated with the column headers, you can use this for the Day #:

N8 =INDEX($A:$A,MAX(IF(($B1:$G1=N7)*($B2:$G11=$K7),ROW($B2:$G11)))) Ctrl Shift Enter
Drag to the right.

See attachment.

Originally Posted by 63falcondude

I have a feeling that the requirements are going to continue to change until I can no longer help but I'll hang in there for now.

Haha that's all the requirements (for now). Your help has been invaluable. I am just getting to grips with Excel so here is a lot I don't know yet.

You're welcome. Happy to help.