Airtable is a relational database software, not a spreadsheet. As such, you have to explicitly link records together for Airtable to know what you want to look up.
It sounds like you need four tables: Companies, Vendors, Requests, and Contacts. It is possible, depending on how you prefer to divy up your data, that Companies and Vendors could occupy a single table and use a “company type” field to differentiate vendors from suppliers.
The Requests table would have two link to another record-type fields: one to link to the Companies table, and one to link to the Vendors table. The Contacts table would have a link to another record-type field connected to the Vendors table (and potentially another one connecting to Companies, if you store contacts for both vendors and companies and if vendors and companies are separate tables). That way the Requests table could have a lookup-type field to pull in any linked Vendors’ contacts.
