# 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

#1
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:L39On the RibbonSelect the Home TabSelect Conditional FormattingSelect 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 DuplicateYou can select your own colors or leave the default.All your Duplicate values will now turn color.MIKEhttp://www.skeptic.com/

Report •

#2
January 5, 2012 at 12:32:49
 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...=CELL("address",INDEX(A1:A100,MATCH("0C01",A1:A100,0)))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 Application.Volatile '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 Next 'Return addresses if value found If tmpStr <> "" Then 'Delete leading comma from address string MyFind = Right(tmpStr, Len(tmpStr) - 2) Else MyFind = "Value Not Found" End If End Function```Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.