Address book and Excel

Dwic

Registered
I would like to use a vlookup function in Excel to refer to my mac address book. I'm constantly cutting, pasting contact info when this should be just an external data lookup. Not exporting or importing but a lookup reference. The address book is just a flat data file, this should be an easy task.

Thank you,
Devin Wickens
wickens.devin@gmail.com
 
See if any of these common causes are ones you have:
Frequent causes:
1. Some cells look like numbers, but are actually text. You can check with the ISTEXT function.

Check both the search arguments and the lookup table. Formatting as numbers afterwards doesn't help.

Remedy: Format an empty cell as Number. Enter the number 1. Edit>Copy. Select your "numbers". Edit>Paste Special, check Multiply.

2. The data is not sorted ascending and the 4th argument of the VLOOKUP is TRUE or is omitted.

3. There are spaces or other invisible characters in either the search arguments or the lookup table.
This often happens when you import data from other applications.
  • Use the LEN() function to see how many characters there really are in the cell and compare that with what you see.
  • Use the TRIM function to remove all spaces except single spaces between words.
  • Use the CLEAN function to remove all nonprintable characters. HTML characters can be removed with a macro by David McRitchie, which can be downloaded here: http://www.mvps.org/dmcritchie/excel/join.htm#trimall
4. The formula was copied from somewhere else, but the addresses of the lookup table were not absolute so have changed in the Paste process and now point to the wrong range.
  • Use absolute addresses for the lookup table, like $A$1:$B$20 instead of relative addresses like A1:B20.
  • When editing or entering a formula, use the F4 key to toggle between several forms of relative addresses and absolute ones (normally only for the table, not for the search argument, but this depends on your specific problem).
  • The first hit of F4 changes the default relative address to an absolute one. That is usually what you need.
  • Even better: use a Defined Name for the table instead of cell addresses; Insert>Name>Define.
5. The table was extended after its initial use, but the definition of the table in the VLOOKUP or in the Defined Name was
not adjusted accordingly.

Remedy:To prevent this from happening: always use explicit bottom and top rows (with dummy error values if necessary) and insert new rows or cells between those two. Then the definition of the range or the Defined Name will adjust automatically.

Users of Excel 2003 and higher may profit from the List feature in the Data menu to maintain tables.​
 
This does not relate to my question. I want to refer to the data in my address book. Is it possible to refer to address book data from an outside application? I do not want to do an export/import, I want to directly reference my address book. Something along the lines of:

=vlookup("Bob Smith",#MY ADDRESS BOOK#,1,)

where #MY ADDRESS BOOK# is a reference to my Mac Address Book.
 
I think asking a Microsoft application to interface nicely with the data in an Apple application is, indeed, and unfortunately, too much to ask.

Microsoft could use the standard APIs in Mac OS X to do Address Book lookups (like many other programs easily do), but they have decided not to, at least with Microsoft Office. This could be due to wanting to retain as little difference between the two Office platforms as possible, but judging by the differences in the latest versions, that may not be likely.

I do think it would be possible to write an AppleScript that would provide some level of the functionality you're looking for... here's Microsoft's AppleScript reference for various Office applications:

http://www.microsoft.com/mac/developers/default.mspx

Perhaps an AppleScript, that, when double-clicked, would export the Address Book data, import it into an Excel document that's hidden from view, then perform a vlookup from the original Excel document to the hidden one. Or something to that effect.
 
Okay, I get it... Apple and Microsoft do not play well together. But what about an Apple product? Can I write a vlookup formula in iWork Numbers that can directly access my Address Book?
 
Back
Top