Excel: Finding Cell that Contains a Value and Displaying its Contents


Hello, I was wondering if anyone could help me with my task? I basically have a large range of data both vertically and horizontally. The contents from cell A-O horizonally contain various different file paths, but I am only interested in finding the path with a certain ZIP file mentioned within it. I have posted a much smaller version as an example of my task below.

[TR] [TD="class: xl65, width: 159"]FilePath1
[TABLE="width: 159"]
[/TD] [TD="class: xl65, width: 154"]FilePath2
[/TD] [TD="class: xl65, width: 194"]FilePath3
[/TD] [TD="class: xl65, width: 162"]Desired Results Path
[/TD] [/TR] [TR] [TD="class: xl65, width: 159"]C:\Docs.RAR\photo1.jpg
[/TD] [TD="class: xl65, width: 154"]C:\Docs.Rar\Photo2.jpg
[/TD] [TD="class: xl65, width: 194"]C:\Zip.ZIP\File.doc
[/TD] [/TR] [TR] [TD="class: xl65, width: 159"]C:\Zip.ZIP\File7.doc
[/TD] [TD="class: xl65, width: 154"]C:\Docs.RAR\File2.doc
[/TD] [TD="class: xl65, width: 194"]C:\Docs.RAR\photo5.jpg
[/TD] [/TR] [TR] [TD="class: xl65, width: 159"]C:\Docs.RAR\FinalExam5.doc
[/TD] [TD="class: xl65, width: 154"]C:\Zip.ZIP\new.jpg
[/TD] [TD="class: xl65, width: 194"]C:\Docs.RAR\facebook.txt
[/TD] [/TR] [/TABLE] Basically I would want to search rows A2-C2 within that example and just display the value containing "Zip.ZIP" into cell D2. So in this case I would want to see C:\Zip.ZIP\File.doc in cell D2. Then I would want to run that same formula on the 3rd row and see the value C:\Zip.ZIP\File7.doc from cell A3 in cell D3, then the value from B4 in D4 and so on... Is there a way I can run searches along a horizontal plane and display only the contents of the cell containing the value I'm interested in within column D? Or is there an easier solution that I haven't thought of? Thanks


This question generated 11 answers. To proceed to the answers, click here.

This thread is current as of September 14, 2014.


For more resources for Microsoft Excel