0

I have a somewhat complicated Vlookup formula that I am trying to write for this particular problem.

I have two excel workbooks dealing with a list of book titles. The only common data column between them is ISBN numbers but one list is much longer i.e. has many more ISBNS than the latter and I suspect that some, if not all, the ISBNS in the smaller workbook are also present in the larger workbook.

What formula should I write to compare the two columns in the separate workbooks so that I can a) immediately identify the ISBNS present in both workbooks and B) know how many are present on both lists and how many are unique?

I hope this makes sense and any help would be greatly appreciated!

pnuts
  • 58,317
  • 11
  • 87
  • 139
maxwell
  • 2,313
  • 23
  • 35

1 Answers1

1

Add a column and use the following formula:

=ISNUMBER(MATCH(A1,H:H,0))

where A1 is an ISBN number, and H:H would be the range that you want to see if the ISBN exists in. It will return TRUE if it exists, and FALSE when it doesn't

Ken
  • 1,156
  • 1
  • 6
  • 8
  • Hey Ken, Thanks I'm getting close to the solution I think - it was indeed searching for the value and returning true or false. How do you set up the formula so that the range it is searching for stays static? When I drag the formula down it goes from A1 to A2 to A3 etc. and stays dynamic which I want but I want the search criteria to not be dynamic i.e. stay as b2-b150 throughout the whole thing – maxwell Jun 17 '14 at 22:29
  • Nevermind - found it here : http://stackoverflow.com/questions/2156563/how-to-keep-one-variable-constant-with-other-one-changing-with-row-in-excel Thank you for your earlier help though I would upvote if I had the rep points :/ – maxwell Jun 17 '14 at 22:45