Solved Formula to return the cell(s) that a value appears in

January 5, 2012 at 11:05:38
Specs: Windows 7
I have a spreadsheet with data in cells B4:L39, and some of the data is duplicated in more than one cell. I want to create a formula that will return the cell number(s) that a specific piece of data is located in. Each cell only has one piece of data.

For example, I have "0C01" that appears in two different cells (B4 and L15). Is there a formula that can be written that will return B4 and L15 instead of me having to manually scan each cell for it?

Thanks for any help you can provide!

See More: Formula to return the cell(s) that a value appears in

Report •

January 5, 2012 at 11:58:21
If your using 2007 you could try Conditional Formatting to change the color of the duplicate cells as a visual indicator.

First highlight your cell range B4:L39
On the Ribbon
Select the Home Tab
Select Conditional Formatting
Select Highlight Cells Rules ( Top of the list)
Select Duplicate Values (Bottom of the list)
In the small drop down window, make sure the left side box reads Duplicate
You can select your own colors or leave the default.

All your Duplicate values will now turn color.


Report •

January 5, 2012 at 12:32:49
✔ Best Answer
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
    End If
'Return addresses if value found
   If tmpStr <> "" Then
'Delete leading comma from address string
     MyFind = Right(tmpStr, Len(tmpStr) - 2)
     MyFind = "Value Not Found"
   End If
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 5, 2012 at 12:42:15
This worked perfectly, thanks a ton!

Yep, nice to reaffirm my Excel competence is bordering on nil. :)

Report •

Related Solutions

Ask Question