VLOOKUP between workbooks, don't return results matching a 2nd column criteria?

Hi,

As part of a procedure I'm writing there will be an exact match check between two separate workbooks of data, e.g. Book1 and Book2.

Basically the goal is to see if any of the values entered in "Column A" in Book1 exist in Book2 "Column A" and return those. A simple VLOOKUP would suffice here except there is data I don't want returned as "matched" in Book2 that are marked to be excluded in a "Column B". Duplicate matches are ok.

E.g.

Book1:

Column A

Value1

Value2

Value3

Value4

Value4

Book2:

Column A	Column B
Value1
Value2	Exclude
Value3
Value4
Value4

I'm looking for a solution that would in this example case return Value1, Value3, Value4 as matches but not Value2, is that possible with a formula?

E.g.

Book1 (with result):

Column A	Column B(vlookup)
Value1	Value1
Value2	#N/A
Value3	Value3
Value4	Value4
Value4	Value4

Set your lookup range to include Column B. Lookup Value in column A return column2 and if it says "Exclude" then "#N/A" otherwise vlookup value and use column1.

Does this make sense to you?

Try something like this..

Formula:

=IF(VLOOKUP($A2,Sheet2!$A$2:$B$6,2,0)="Exclude",NA(),IFERROR(VLOOKUP($A2,Sheet2!$A$2:$B$6,1,0),"Actually NA()"))

Change the range of sheet 2 accordingly..