title: Match text from two different workbooks, return value from different column
body: <p>I have never been so frustrated with Excel in my life. I'm coming up against two major deadlines at work, and the only way to get the right info to the right people involves solving the problem below.</p>
  <p>I have two workbooks with different metrics for different &quot;clients&quot; in each, and I need to get all of the info into one sheet - can't simply copy/paste the data over because I could have Client A in the first workbook, but not in the second. Also, can't use &quot;Client Name&quot; as the unique identifier between workbooks because one could be &quot;The John Smith Company&quot; and the other could say, &quot;The John Smith Co.&quot;</p>
  <p>Here is a very simplified example:</p>
  <p>Workbook A</p>
  <ul>
  <li>Client ID</li>
  <li>Number of Employees</li>
  </ul>
  <p>Workbook B</p>
  <ul>
  <li>Client ID</li>
  <li>Years as Client</li>
  <li>Location</li>
  <li>Client Name</li>
  </ul>
  <p>Before you mark me as a duplicate question, know that I have tried the following formulas to no avail:</p>
  <blockquote>
  <p>=VLOOKUP(D2,[othersheet.xlsx]sheet1!$A:$D,2,FALSE)</p>
  <p>=IF(ISERROR(EXACT(D2,[othersheet.xlsx]sheet1!$1:$1048576)), &quot;True&quot;, VLOOKUP(D2,[othersheet.xlsx]sheet1!$1:$1048576,2,FALSE))</p>
  </blockquote>
  <p>Losing my mind here - Any ideas would be marvelous. Thank you in advance!</p>
  <p><strong>ETA:</strong></p>
  <p><strong>I'm using Excel 2011 for Mac, v. 14.4.1.</strong></p>