What is the difference between AttributeValue and Lookup for personalisation, and when do you use each?

AttributeValue("attrName") retrieves a profile attribute or sendable data extension field value for the current subscriber being rendered. It looks up the value from the subscriber's profile attributes (All Subscribers list attributes) or, when sending from a data extension, from the columns in that DE. It is the fastest method for simple personalisation.
Lookup("DE", returnCol, matchCol, matchVal) performs an explicit query against any data extension. Use Lookup when you need to retrieve data from a non-sendable DE, a related record, or when you need fields not available on the subscriber's send record. Lookup incurs more processing overhead; cache results in variables when re-used multiple times in the same email.