Find and return cell address

[RHH1095]

I am looking for a function that will search a group of cells (Say -- A1:D15) for a specific value located in another cell (Say -- E15) and return the Cell Address where the match was found from within A1:D15.

The values in the "array" A1:D15 can be both text and numbers.

Thank you in advance.
RHH1095

 

[Robert Mika]

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style=";">papa</td><td style="text-align: right;;"></td><td style=";">$A$9</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="background-color: #4F81BD;;">papa</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;background-color: #4F81BD;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table>

Sheet2​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Array Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">G1</th><td style="text-align:left">{=ADDRESS(MIN(IF(A1:D15=E1,ROW(A1:D15))),MIN(IF(A1:D15=E1,COLUMN(A1:D15))))}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>

 

[RHH1095]

Thank you! Worked perfectly!

 

[kamran02]

1 thing i must want to say about Robert that he is really genius

 

[Robert Mika]

Thank you kamran for your kindly comment but I'm just average user.
There a lot of people here who certainly can be call geniuses.

 

[kamran02]

Hmm... So should i take my words back...? hah aha
Yes you are absolutely right! So i am taking my words back :P

 

[RHH1095]

OK, I have another question... Yes, a slight level of laziness on my part. If my next "array" of the same size is to the right of the first array(and so for), how do format the formula so when I drag the formula to the next cell it it updates (indexes) with much of a manual effort? If this question makes sense.

 

[RHH1095]

Robert, I did take the time to read and review help based on your suggestion. I now understand arrays (Know of their existance!). I was struggling with many functions only working on a single column or row. Ctrl-Shift-Return Yeah!

 

[Robert Mika]

Robert, I did take the time to read and review help based on your suggestion. I now understand arrays (Know of their existance!). I was struggling with many functions only working on a single column or row. Ctrl-Shift-Return Yeah!

Does this mean that you question has been answered?

 

[Warship]

Just for kicks - a VBA solution:

Function GetCellAddress(rng As String, srchVal As String) As String
    Dim found As Range
    Set found = Range(rng).Find(srchVal, , , xlWhole)
    If Not found Is Nothing Then
        GetCellAddress = found.Address(0, 0)
    Else
        GetCellAddress = srchVal & " not found"
    End If
End Function

Enter into cell: =GetCellAddress("A1:D15",E15)