software

I have a worksheet showing a column of file names e.g.
XY1234X
XY5678X
XY2468X
XY4321X etc

and another worksheet containing a column of file paths e.g.
I:\Sales\AllUsers\Marketing\115001 - North East\XY1234X
I:\Sales\AllUsers\Marketing\115001 - North East\XY8765X
I:\Sales\AllUsers\Marketing\115002 - North\XY4321X
I:\Sales\AllUsers\Marketing\115003 - North West\XY5678XWhat I'd like to do is cross reference them to see if any of the file
names appear in any of the paths.

I tried using VLOOKUP by adding a column to the right of the path
column with the letter quot;Yquot; in it and asking for this quot;Yquot; to be returned
in the event that the file name appeared in the path but it returned
quot;#N/Aquot; indicating that the data was not found (even though the test
Lookup value was definitely contained in one of the paths in the table
array).

I'm pretty sure I'm barking up the wrong tree with VLOOKUP and would
maybe be better off with an IF function of some kind. Does any one
have any ideas, please?--
SRL
------------------------------------------------------------------------
SRL's Profile: www.excelforum.com/member.php...foamp;userid=6748
View this thread: www.excelforum.com/showthread...hreadid=516137SRL,

With the file names starting in cell A2, and paths on sheet2 in column A:

=IF(ISERROR(MATCH(quot;*quot;amp;A2,Sheet2!$A:$A,FALSE)),quot;No
matchquot;,INDEX(Sheet2!A:A,MATCH(quot;*quot;amp;A2,Sheet2!$A:$A, FALSE)))

Copy down to match your list of file names.

HTH,
Bernie
MS Excel MVPquot;SRLquot; gt; wrote in message
...
gt;
gt; I have a worksheet showing a column of file names e.g.
gt; XY1234X
gt; XY5678X
gt; XY2468X
gt; XY4321X etc
gt;
gt; and another worksheet containing a column of file paths e.g.
gt; I:\Sales\AllUsers\Marketing\115001 - North East\XY1234X
gt; I:\Sales\AllUsers\Marketing\115001 - North East\XY8765X
gt; I:\Sales\AllUsers\Marketing\115002 - North\XY4321X
gt; I:\Sales\AllUsers\Marketing\115003 - North West\XY5678X
gt;
gt;
gt; What I'd like to do is cross reference them to see if any of the file
gt; names appear in any of the paths.
gt;
gt; I tried using VLOOKUP by adding a column to the right of the path
gt; column with the letter quot;Yquot; in it and asking for this quot;Yquot; to be returned
gt; in the event that the file name appeared in the path but it returned
gt; quot;#N/Aquot; indicating that the data was not found (even though the test
gt; Lookup value was definitely contained in one of the paths in the table
gt; array).
gt;
gt; I'm pretty sure I'm barking up the wrong tree with VLOOKUP and would
gt; maybe be better off with an IF function of some kind. Does any one
gt; have any ideas, please?
gt;
gt;
gt; --
gt; SRL
gt; ------------------------------------------------------------------------
gt; SRL's Profile: www.excelforum.com/member.php...foamp;userid=6748
gt; View this thread: www.excelforum.com/showthread...hreadid=516137
gt;