CODEWITHSUNDEEP
excelexcel-formula
Aurelius
Aurelius

Reputation: 485

Searchng a sheet for a value and returning row & column numbers

I need to output the row number and column number for a cell that contains a specific word or phrase.

This could be in any cell (Any row, any column) on a sheet.

To output the row I have:

=ROW(INDEX(INDIRECT("'"&$AA$2&"'!"&"A:ZZ"),MATCH("MY WORD",INDIRECT("'"&$AA$2&"'!"&"A:ZZ"),0)))

and for column I have:

=COLUMN(INDEX(INDIRECT("'"&$AA$2&"'!"&"A:ZZ"),MATCH("MY WORD",INDIRECT("'"&$AA$2&"'!"&"A:ZZ"),0)))

Now I know the above will not work as multiple columns do not work in MATCH

For some clarification: The sheet the cell will be on is dynamic (Could be present or not present) so I am having to use INDIRECT Cell AA2 contains the output of a function that returns the dynamic sheet name, if it is present. The outputs of the above will go into an ADDRESS function which is a helper column for a larger array formula.

How else can I get this output?

Upvotes: 1

Views: 203

Answers (2)

robrados
robrados

Reputation: 144

Try the following formula. Please note that this is an array formula and that you have to confirm it with Ctrl+Shift+Enter instead of only pressing the Enter-key.

=IFERROR(SMALL(IF(ISNUMBER(SEARCH($D$7,$A$1:$B$6)),1*(ROW($A$1:$B$6)&"."&COLUMN($A$1:$B$6)),""),ROW($A1)),"")

Drag the formula dow

Adjust the range A1:B6 to your data range, enter the formula and drag the formula down. the output of the formula is the position of the word you are searching for in the format R.C (Row.Column), if there are no more matches the output of the formula will be empty.

Note that if you want to have a case-sensitive search function you need to use the FIND() function, the SEARCH() function is not case sensitive.

If you want to transform the output to the address then use the following formula next to the R.C output:

=ADDRESS(LEFT(D9,SEARCH(".",D9)-1),MID(D9,SEARCH(".",D9)+1,LEN(D9)))

Upvotes: 2

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

If you're not adverse to using VBA this custom array function will return the row & column number: 

Public Function RowNum(TextToSearch As String, SheetRef As Range) As Variant

    Dim rFind As Range

    Application.Volatile True

    With SheetRef.Parent
        Set rFind = .Cells.Find(What:=TextToSearch, _
                                After:=.Cells(1, 1), _
                                LookIn:=xlValues, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
        If Not rFind Is Nothing Then
            RowNum = Array(rFind.Row, rFind.Column)
        Else
            'Remove if you want function to return 0 on not found.
            RowNum = CVErr(xlErrNA)
        End If

    End With

End Function

Finds the first instance of "My Word" anywhere on sheet 2: {=RowNum("My Word",Sheet2!A1)}

Find the first instance of "My Word" anywhere on the current sheet: {=RowNum("My Word",B2)}

Upvotes: 1

Related Questions