Matching up data between 2 workbooks

[zookeepertx]

I have 2 workbooks: 1 contains data in columns A-K & the other is a log containing data in columns A-P. On the data sheet, col C is a vendor name & col A is a shortened code for the vendor. On the log sheet, col E is the first 9 characters of the vendor name followed by a / & the vendor number.

I need to match up the 9 characters of the vendor name on the log with the first 9 characters of the vendor name on the data sheet & return the vendor's code from col A of the data sheet in col R of the log.
EXCEPT: if the vendor name has a duplicate just above or below it on the data sheet or the vendor code has a duplicate just above or below it on the data sheet, then I need the word VERIFY in col R of the log. It would be best if the cells that return VERIFY could be red & any cells in col R without a match on the data sheet could be yellow with something like NO MATCH in them.

Here is an example of the data sheet (left) & the log sheet (right) as they start out:

	Col A	Col C				Col E
Row 1	WIDS	Widgets and Stuff			Row 1	Justin Mo/27584
Row 2	BUCO	Bungee Cords Intl			Row 2	Wires N S/13579
Row 3	WNST	Wires N Strings			Row 3	Dictionar/14893
Row 4	DICT	Dictionaries R Us			Row 4	Dictionar/14893
Row 5	DICR	Dictionaries R Us			Row 5	Northern /98347
Row 6	JUST	Just A Dollar			Row 6	Widgets a/12345
Row 7	JUST	Justin Morgan			Row 7	Bungee Co/24680

<TBODY>
</TBODY>

As you can see, Dictionaries R Us has a duplicate in the name column & JUST has a duplicate in the code column. Also, on the log sheet, Row 5 has no match on the data sheet. So, the desired result on the log sheet would be:

	Col E	Col R
Row 1	Justin Mo/27584	VERIFY
Row 2	Wires N S/13579	WNST
Row 3	Dictionar/14893	VERIFY
Row 4	Dictionar/14893	VERIFY
Row 5	Northern /98347	NO MATCH
Row 6	Widgets a/12345	WIDS
Row 7	Bungee Co/24680	BUCO

<TBODY>
</TBODY>

I would REALLY appreciate any help on this; I've been studying on it & can't seem to figure out how to do it. Thanks!
(Just got Excel 2010 & still trying to figure it out)