Excel 2007 : Vlookup Help

[markharper80]

Good day all,
Thank you in advance for your help.

I have a workbook (we'll call A) showing me loan numbers, associate names and Decision Status (all of which have been filtered to show Open Conditions remain). I need scrubbed against this list another workbook (we'll call B) to find out which of these in A have Exceptions requested so they can be removed from the first workbook or Master. Workbook B simply contains loan numbers, Decision status (filtered to Open conditions) and whether the Exception is requested.
My first attempt was to create the following vlookup formula in A under the Open Conditions tab (since this is a common column found in both) then simply filter out the NAs and delete them.

=vlookup(A2,'SheetB'!A9:C12000,3,false)

A2= Loan number - lookup value
A9 = C12000 on Sheet B are loan numbers, exception granted, and open conditions - table array
Colum index 3 is open conditions -

This returns results of all NAs. Now I know for a fact that there are at least 200 files that can be found as duplicates.

I have tried various version of this and cannot seem to get the desired result.

I hope that is enough info. Thank you again

[NBVC]

Perhaps, if you have loan numbers, then they may not be formatted properly as numbers.

Try selecting column A of SheetB and then go to Data|Text to Columns.. then click Finish.

[markharper80]

Thanks for the quick reply. no luck udpating the text to columns though.

[NBVC]

How about?

=vlookup(A2+0,'SheetB'!A9:C12000,3,false)

[markharper80]

Still no luck. Here is a dummy excel sheet with what I am doing. Thanks again for your help.

I am trying to find any matches from B in A so that I may remove them from A.

[Cutter]

If you put absolute referencing on the B! range you'll find there are 101 matches when you drag your formula down. So use:

=VLOOKUP(A2,B!$A$2:$C$166,2,FALSE)

matches are found from rows 156 to 256

[markharper80]

That got it, thank you!