Finding matching cell data

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\XY5678X


What 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 "Y" in it and asking for this "Y" to be returned in the event that the file name appeared in the path but it returned "#N/A" 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,

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

=IF(ISERROR(MATCH("*"&A2,Sheet2!$A:$A,FALSE)),"No
match",INDEX(Sheet2!A:A,MATCH("*"&A2,Sheet2!$A:$A,FALSE)))

Copy down to match your list of file names.

HTH,
Bernie
MS Excel MVP


"SRL" <[email protected]> wrote in message
news:[email protected]...
>
> 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\XY5678X
>
>
> What 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 "Y" in it and asking for this "Y" to be returned
> in the event that the file name appeared in the path but it returned
> "#N/A" 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: http://www.excelforum.com/member.php...fo&userid=6748
> View this thread: http://www.excelforum.com/showthread...hreadid=516137
>