Vlookup between 2 workbooks, taking into account duplicates on source.

[zookeepertx]

I've got 3 columns of formulas that end up doing what I need, but I'm thinking it could probably be done easier with VBA.

I have 1 book with a sheet I'll call Log & another book with a sheet I'll call Source. On the Log, column E has the first 9 characters of vendor names & /vendor number (ex: EDMUND FI/00250), along with other data out through column P. On Source, the vendor names are in C & a short code for their name is in A.

I need to compare the first 9 characters in Log col E with the first 9 characters in Source col C. When a match is found, I need to return the short code from Source col A. BUT - if there's a duplicate in either Source col C or A, instead I need to return the word VERIFY, preferably with the cell highlighted in red.

Right now, I have, on Source col L:

=MID(C2,1,9)

and in source M:

=IF(OR(A2=A1,A2=A3,L2=L1,L2=L3),"VERIFY",A2)

This gives me the first 9 characters of the vendor name in L & the short code OR VERIFY in M

Then, in Log col R, I have an array formula:

=VLOOKUP(MID(E4,1,9),'[PRETICKET P ADDRESSES working.xls]Paddress'!$L:$M,2,FALSE)

This gives me either the short code or VERIFY from Source M. Also, sometimes it doesn't find a match & it returns #N/A. Then I have to do Conditional Formatting to make the VERIFY cells red & I thought I'd make the error cells be yellow (although I haven't figured out the CF for that yet)

Is there a better way to do this with VBA? I'm working with Excel 2010; just got it & still figuring it out.

Thanks!!

 

[zookeepertx]

Here is a short example of the Source sheet:

Col A	Col C	Col L	Col M
WIDS	Widgets and Stuff	Widgets a	WIDS
BUCO	Bungee Cords Intl	Bungee Co	BUCO
WNST	Wires N Strings	Wires N S	WNST
DICT	Dictionaries R Us	Dictionar	VERIFY
DICR	Dictionaries R Us	Dictionar	VERIFY
JUST	Just A Dollar	Just A Do	VERIFY
JUST	Justin Morgan	Justin Mo	VERIFY

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><TBODY>
</TBODY>

and the Log sheet result desired:

Col E	Col R
Justin Mo/27584	VERIFY
Wires N S/13579	WNST
Dictionar/14893	VERIFY
Dictionar/14893	VERIFY
Northern /98347	#N/A
Widgets a/12345	WIDS
Bungee Co/24680	BUCO

<COLGROUP><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY>
</TBODY>

Thanks for any help!

 

[zookeepertx]

I just realized that the array formula in Log col R should say (if following the information I gave):

=VLOOKUP(MID(E4,1,9),'[Source]Sheet1'!$L:$M,2,FALSE)

Sorry about that!