Finding Cell that Contains a Value and Displaying its Contents

G

GreyEyedWolf

New Member
Joined
Jul 26, 2014
Messages
14
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.


FilePath1

<tbody>
</tbody>
FilePath2

<tbody>
</tbody>
FilePath3

<tbody>
</tbody>
Desired Results Path

<tbody>
</tbody>
C:\Docs.RAR\photo1.jpg

<tbody>
</tbody>
C:\Docs.Rar\Photo2.jpg

<tbody>
</tbody>
C:\Zip.ZIP\File.doc

<tbody>
</tbody>
C:\Zip.ZIP\File7.doc

<tbody>
</tbody>
C:\Docs.RAR\File2.doc

<tbody>
</tbody>
C:\Docs.RAR\photo5.jpg

<tbody>
</tbody>
C:\Docs.RAR\FinalExam5.doc

<tbody>
</tbody>
C:\Zip.ZIP\new.jpg

<tbody>
</tbody>
C:\Docs.RAR\facebook.txt

<tbody>
</tbody>

<tbody>
</tbody>


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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Ok, I'm sure there must be a simpler way, but here is my solution:


Excel 2010
ABCD
1FilePath1FilePath2FilePath3Desired Results Path
2C:\Docs.RAR\photo1.jpgC:\Docs.Rar\Photo2.jpgC:\Zip.ZIP\File.docC:\Zip.ZIP\File.doc
3C:\Zip.ZIP\File7.docC:\Docs.RAR\File2.docC:\Docs.RAR\photo5.jpgC:\Zip.ZIP\File7.doc
4C:\Docs.RAR\FinalExam5.docC:\Zip.ZIP\new.jpgC:\Docs.RAR\facebook.txtC:\Zip.ZIP\new.jpg
Sheet2
Cell Formulas
RangeFormula
D2=INDEX(A2:C2,,MATCH("zip.zip",CHOOSE({1,2,3},TRIM(MID(SUBSTITUTE(A2,"\",REPT(" ",100)),100,100)),TRIM(MID(SUBSTITUTE(B2,"\",REPT(" ",100)),100,100)),TRIM(MID(SUBSTITUTE(C2,"\",REPT(" ",100)),100,100))),0))
</table><br />
 
Upvote 0
I'm confused. Tell me the cell references of all of the lookup values. Then tell me the cell reference of the search range. Does the search only look for the lookup value in a row or the entire range?
 
Upvote 0
WarPiglet said:
I'm confused. Tell me the cell references of all of the lookup values. Then tell me the cell reference of the search range. Does the search only look for the lookup value in a row or the entire range?
Click to expand...

Only within the row horizontally, not looking vertically at all. I know there is going to be one instance of the file I'm looking for within the single row. Thanks
 
Upvote 0
bschwartz said:
Ok, I'm sure there must be a simpler way, but here is my solution:
Click to expand...

I am also sure there's a much simpler approach than mine too :)


Excel 2010
ABCD
1FilePath1FilePath2FilePath3Desired Results Path
2C:\Docs.RAR\photo1.jpgC:\Docs.Rar\Photo2.jpgC:\Zip.ZIP\File.docC:\Zip.ZIP\File.doc
3C:\Zip.ZIP\File7.docC:\Docs.RAR\File2.docC:\Docs.RAR\photo5.jpgC:\Zip.ZIP\File7.doc
4C:\Docs.RAR\FinalExam5.docC:\Zip.ZIP\new.jpgC:\Docs.RAR\facebook.txtC:\Zip.ZIP\new.jpg
Sheet1
Cell Formulas
RangeFormula
D2{=INDEX(A2:C2,1,MATCH(TRUE,IFERROR(SEARCH("Zip.Zip",A2:C2),0)>0,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Here is a simpler version:


Excel 2010
ABCD
1FilePath1FilePath2FilePath3Desired Results Path
2C:\Docs.RAR\photo1.jpgC:\Docs.Rar\Photo2.jpgC:\Zip.ZIP\File.docC:\Zip.ZIP\File.doc
3C:\Zip.ZIP\File7.docC:\Docs.RAR\File2.docC:\Docs.RAR\photo5.jpgC:\Zip.ZIP\File7.doc
4C:\Docs.RAR\FinalExam5.docC:\Zip.ZIP\new.jpgC:\Docs.RAR\facebook.txtC:\Zip.ZIP\new.jpg
Sheet2
Cell Formulas
RangeFormula
D2=INDEX(A2:C2,,MATCH(2^15,CHOOSE({1,2,3},SEARCH("zip.zip",A2),SEARCH("zip.zip",B2),SEARCH("zip.zip",C2))))
 
Upvote 0
bschwartz said:
Here is a simpler version:

Excel 2010
ABCD
1FilePath1FilePath2FilePath3Desired Results Path
2C:\Docs.RAR\photo1.jpgC:\Docs.Rar\Photo2.jpgC:\Zip.ZIP\File.docC:\Zip.ZIP\File.doc
3C:\Zip.ZIP\File7.docC:\Docs.RAR\File2.docC:\Docs.RAR\photo5.jpgC:\Zip.ZIP\File7.doc
4C:\Docs.RAR\FinalExam5.docC:\Zip.ZIP\new.jpgC:\Docs.RAR\facebook.txtC:\Zip.ZIP\new.jpg

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2​

Worksheet Formulas
CellFormula
D2=INDEX(A2:C2,,MATCH(2^15,CHOOSE({1,2,3},SEARCH("zip.zip",A2),SEARCH("zip.zip",B2),SEARCH("zip.zip",C2))))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Click to expand...

Thank you! The only downside with this formula would be the time it takes to type it. If I have a much larger range, say columns A-Z it would take a while to enter it all in. Other than that it's perfect.
 
Upvote 0
GreyEyedWolf said:
Thank you! The only downside with this formula would be the time it takes to type it. If I have a much larger range, say columns A-Z it would take a while to enter it all in. Other than that it's perfect.
Click to expand...

Don't you think my suggestion in post 4 will be shorter to type? and you only need to change column C to the last column of data :)
 
Upvote 0
GreyEyedWolf said:
Thank you! The only downside with this formula would be the time it takes to type it. If I have a much larger range, say columns A-Z it would take a while to enter it all in. Other than that it's perfect.
Click to expand...

In that case, you can use an array formula like the one posted here by Momentman:

Momentman said:
I am also sure there's a much simpler approach than mine too :)


Excel 2010
ABCD
1FilePath1FilePath2FilePath3Desired Results Path
2C:\Docs.RAR\photo1.jpgC:\Docs.Rar\Photo2.jpgC:\Zip.ZIP\File.docC:\Zip.ZIP\File.doc
3C:\Zip.ZIP\File7.docC:\Docs.RAR\File2.docC:\Docs.RAR\photo5.jpgC:\Zip.ZIP\File7.doc
4C:\Docs.RAR\FinalExam5.docC:\Zip.ZIP\new.jpgC:\Docs.RAR\facebook.txtC:\Zip.ZIP\new.jpg
Sheet1
Cell Formulas
RangeFormula
D2{=INDEX(A2:C2,1,MATCH(TRUE,IFERROR(SEARCH("Zip.Zip",A2:C2),0)>0,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
If a cell contains Text, return a money value example 300 in next coloum
Replies
8
Views
465
DarrenBurke
D
P
  • Question
Displaying the contents of a cell based on value if others
Replies
1
Views
462
Paul Naylor
P
T
  • Question
Cell 1 Contains "", Cell 2 Contains "", return value from matrix in Cell 3 (matrix displayed on another sheet)
Replies
1
Views
309
kweaver
kweaver
S
  • Question
VBA Clear Cell if
Replies
2
Views
475
Joe4
Joe4
M
  • Question
Display match/cell contents if lookup contains specific text
Replies
2
Views
634
MrSamExcel
M

Forum statistics

Threads
1,214,551
Messages
6,120,161
Members
448,948
Latest member
spamiki

Share this page

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