Index and Match formula to return header that contains the min value in range

[goblue667]

Hello i am really struggling with this.

I have a macro that takes raw data into a certain form. My range is AK:CH. However, there are certain columns that I do not want to include in that range. The columns I want to evaluate contains the text "Height".

Cells(PoleRow, 3).Formula = "=IFERROR(INDEX($AK$12:$CH$12,1,MATCH(MIN($AK$" & PoleRow & ":$CH$" & PoleRow & "),$AK$" & PoleRow & ":$CH$" & PoleRow & ", 0)),"""")"

or would it be easier to mention specific columns that i do not include ("CC") or is there a way to break up an index array so that it starts and stops before and after a column i do not want??

 

[Eric W]

Welcome to the Board.

How about:

    ad1 = "$AK$12:$CH$12"
    ad2 = "$AK$" & PoleRow & ":$CH$" & PoleRow
    
    Cells(PoleRow, 3).FormulaArray = "=IFERROR(INDEX(12:12,SMALL(IF((SMALL(IF(ISERR(SEARCH(""Height""," & _
                                     ad1 & "))*(" & ad2 & "<>"""")," & ad2 & "),1)=" & ad2 & _
                                     ")*ISERROR(SEARCH(""Height""," & ad1 & ")),COLUMN(" & ad1 & ")),1)),"""")"

I assume that "Height" is found in row 12, if not, change the red addresses to where it is.