Find and return cell address

RHH1095

New Member
Joined
Dec 3, 2011
Messages
12
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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>
 
Upvote 0
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.
 
Last edited:
Upvote 0
Hmm... So should i take my words back...? hah aha
Yes you are absolutely right! So i am taking my words back :P
 
Upvote 0
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.
 
Upvote 0
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! ;-)
 
Upvote 0
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?
 
Upvote 0
Just for kicks - a VBA solution:
Code:
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)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top