|There is no built-in Excel formula to return multiple addresses across a range of cells.|
If we were dealing with a single Column (or Row) and only wanted to return a single address, then using something like this might work...
However, since you want a "list" of addresses, we have to write our own User Defined Function (UDF) to seach through the range and build a string that contains all of the addresses where the value is found.
As written, you would enter the value you are searching for in a cell (e.g. B1) and then enter =MyFind(B1) in any other cell. Make sure that the cell you use the UDF in is wide enough to display the list of addresses.
1 - Use Alt-F11 to open the VBA editor.
2 - Click on Insert...Module and paste this code into the pane that opens.
3 - In your worksheet, enter =MyFind(B1)
You should get something like this:
$B$7, $K$10, $I$13, $G$20, $H$24
Function MyFind(MyVal) As String
'Loop through range
For Each c In ActiveSheet.Range("B4:L39")
'Build string with addresses if value found
If c = MyVal Then
tmpStr = tmpStr & ", " & c.Address
'Return addresses if value found
If tmpStr <> "" Then
'Delete leading comma from address string
MyFind = Right(tmpStr, Len(tmpStr) - 2)
MyFind = "Value Not Found"
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.