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
------------------------------------------------------------------------
SRL's Profile: http://www.excelforum.com/member.php...fo&userid=6748
View this thread: http://www.excelforum.com/showthread...hreadid=516137

Finding matching cell data
 

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" wrote in message
...

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