Find column index

[PabloEscobar]

I am trying to return the column index/letter of a column in sheet2(called SID). Somewhere in the range of A1:Z500 there is only 1 cell that contains the word "Filter Flag". I simply need to find the row column that this word appears in.

I have tried =MATCH("Filter Flag",SID!A1:Z500,0) but that returns #N/A. I've also tried the following long nested if statement to search over each row individually and see if it contains the word and simply return the letter, but that comes back with #N/A also. I think I may have the syntax wrong of the formula?

=IF(MATCH("Filter Flag",SID!A1:A500,0)>0,"A",IF(MATCH("Filter Flag",SID!B1:B500,0)>0,"B",IF(MATCH("Filter Flag",SID!C1:C500,0)>0,"C",IF(MATCH("Filter Flag",SID!D1:D500,0)>0,"D",IF(MATCH("Filter Flag",SID!E1:E500,0)>0,"E",IF(MATCH("Filter Flag",SID!F1:F500,0)>0,"F",IF(MATCH("Filter Flag",SID!G1:G500,0)>0,"G",IF(MATCH("Filter Flag",SID!H1:H500,0)>0,"H",IF(MATCH("Filter Flag",SID!I1:I500,0)>0,"I",IF(MATCH("Filter Flag",SID!J1:J500,0)>0,"J",IF(MATCH("Filter Flag",SID!K1:K500,0)>0,"K",IF(MATCH("Filter Flag",SID!L1:L500,0)>0,"L",IF(MATCH("Filter Flag",SID!M1:M500,0)>0,"M",IF(MATCH("Filter Flag",SID!N1:N500,0)>0,"N",IF(MATCH("Filter Flag",SID!O1:O500,0)>0,"O",IF(MATCH("Filter Flag",SID!P1:P500,0)>0,"P",IF(MATCH("Filter Flag",SID!Q1:Q500,0)>0,"Q",IF(MATCH("Filter Flag",SID!R1:R500,0)>0,"R",IF(MATCH("Filter Flag",SID!S1:S500,0)>0,"S",IF(MATCH("Filter Flag",SID!T1:T500,0)>0,"T",IF(MATCH("Filter Flag",SID!U1:U500,0)>0,"U",IF(MATCH("Filter Flag",SID!V1:V500,0)>0,"V",IF(MATCH("Filter Flag",SID!W1:W500,0)>0,"W",IF(MATCH("Filter Flag",SID!X1:X500,0)>0,"X",IF(MATCH("Filter Flag",SID!Y1:Y500,0)>0,"Y",IF(MATCH("Filter Flag",SID!Z1:Z500,0)>0,"Z",0))))))))))))))))))))))))))

Funny thing is I've tested it when I know this word is in column R, and the above formula returns N/A. I also tried =IF(MATCH("Filter Flag",SID!Q1:Q100)>0,"Q",IF(MATCH("Filter Flag",SID!R1:R500)>0,"R",0)) to test it and even though I know it's in column R for this test, it returns N/A for some reason.

Any help or advice on how to find the column letter of the "Filter Flag" text please?

 

[Aladin Akyurek]

Re: Trivial question to find column index

Control+shift+enter, not just enter:

=SUBSTITUTE(ADDRESS(1,MIN(IF(SID!A1:Z500="Filter Flag",COLUMN(SID!A1:Z500)-COLUMN(SID!A1)+1)),4),1,"")

 

[PabloEscobar]

Re: Trivial question to find column index

Control+shift+enter, not just enter:

=SUBSTITUTE(ADDRESS(1,MIN(IF(SID!A1:Z500="Filter Flag",COLUMN(SID!A1:Z500)-COLUMN(SID!A1)+1)),4),1,"")

Thanks, you are a legend!

Care to give me an ELI5 explanation of it please?

 

[My Aswer Is This]

Re: Trivial question to find column index

Try this Vba script:

Sub Find_Me()
Application.ScreenUpdating = False
Dim c As Range
    
    For Each c In Range("A1:Z500")
        If c.Value = "Filter Flag" Then MsgBox "Filter Flag found in column  " & c.Column: Exit Sub
    Next
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Re: Trivial question to find column index

Try this Vba script:

Sub Find_Me()
Application.ScreenUpdating = False
Dim c As Range
    
    For Each c In Range("A1:Z500")
        If c.Value = "Filter Flag" Then MsgBox "Filter Flag found in column  " & c.Column: Exit Sub
    Next
Application.ScreenUpdating = True
End Sub

You forgot to have the range reference the worksheet the OP said contain the search word (name is SID). If the OP ends up wanting a VBA solution, here is a UDF (user defined function) that he can use to obtain the column letter designation...

[table="width: 500"]
[tr]
	[td]Function FilterFlagCol() As String
  FilterFlagCol = Split(Sheets("SID").UsedRange.Find("Filter Flag", , xlValues, xlWhole, , , False, , False).Address(1, 0), "$")(0)
End Function[/td]
[/tr]
[/table]

 

[Aladin Akyurek]

Re: Trivial question to find column index

Thanks, you are a legend!

You are welcome.

Care to give me an ELI5 explanation of it please?

The IF bit delivers a column number when a cell in A1:Z500 is equal to the string filter flag. The expression COLUMN(SID!A1:Z500)-COLUMN(SID!A1)+1 delivers the relative column number, while here an absolute column number, given by COLUMN(SID!A1:Z500), would be ok too.

MIN returns the smallest from the column numbers IF returns (Mind you, IF might return one or more column numbers.)

The smallest column number is fed to ADDRESS which yields something like R1. SUBSTITUTE deletes 1 from that result, yielding R, the column letter we are after.

Hope this helps.