Comparing multiple columns of two lists

Hey,

I have two workbooks of approximately 26 000 and 13 000 rows. In the first workbook (WB1) each row contains an ID number in column "B", and a document number in column "L". In the second workbook (WB2) column "A" contains a document number, and column "I" an ID number. In column "O" a text, i.e. "Verified" is displayed.

Both ID and document numbers contain both letters and numbers, i.e. "12-3A-BC-D45-678" or "AB1234".


What I need done, is the following:

I want to add a column "P" to WB1. When the "B" and "L" values of a row in WB1 is found in the "I" and "A" columns of any row in WB2, cell "P_" should display "Verified" (the text will be in Norwegian, but all cells in "P" should give either "text" or no value.)

Example:

Capture.PNG


I don't know much visual basics, and have just tried working something out using =IF():

=IF(AND(B6=$U$6:$U$13451;L6=$T$6:$T$13451);"Verified";"")

(I copied columns A and I from WB2 into U and T, WB1)


However, the result seems to be either an empty cell or "#VALUE!". I don't know how to resolve this, and I also suspect that my formula does not demand that the values from B and L, WB1 needs to be found in the same row in WB2.


I would very much appreciate it if anyone has a solution to my problem! Please let me know if the problem is not expressed clearly enough. Btw, I use MS Office Professional Plus 2010.

Regards,
oyvron