HubSpot to Google Sheets: Join Deals and Companies Without a VLOOKUP That Breaks
Deals are in one export and company details are in another, so every report by industry starts with a lookup between two files. Then a rep renames an account and the whole thing returns blanks.
Getting HubSpot into Google Sheets is easy. The part that breaks is the join: deals live in one export, company details in another, and a lookup keyed on the company name fails the day someone renames an account. Key it on the ID instead, count what does not match, and the report survives a rename.
Two exports, one key
Export deals with the associated company's record ID, and companies with their own record ID and the fields you report by, such as industry and region. Put each on its own tab: Deals and Companies.
The join
In Deals, with the company ID in column D, add the industry:
=IFERROR(VLOOKUP(D2, Companies!A:E, 4, FALSE), "no match")
Here column A of Companies is the record ID and column D is the industry, so the key must be the first column of the range. Or with XLOOKUP, which does not have that restriction:
=XLOOKUP(D2, Companies!A:A, Companies!D:D, "no match")
Fill down, then count how many deals returned "no match":
=COUNTIF(E:E, "no match")
The check that catches the silent misses
Industry totals should add up to the total deal count. If they do not, rows were dropped or double-counted. The usual causes:
- Deals with no company. They return "no match". Show them as a row, do not delete them.
- Deals with several companies. The export repeats the deal, so counts are inflated. Keep one primary company per deal.
- A stale Companies tab. A new company created after the export has no row to match. Re-export both tabs together.
See how to use VLOOKUP in Google Sheets for the lookup itself, and how to analyze a CRM export for what to do with the joined table.
Without the two exports
The Quiriz HubSpot connector is read-only and refreshes daily, so there is no export to repeat. Quiriz brings [HubSpot] Deals and [HubSpot] Companies in as two datasets and keeps them current, so there is no export to repeat or tab to refresh. With the Sheets add-in, ask the question directly:
=QUIRIZ("won deal amount by company industry this quarter", "table")
Where this stops. The join still depends on the deal-to-company association in HubSpot. Deals with no company, or with several, need a rule in any tool, so check the first answer against your own count: the industry rows should add up to the total number of deals. If your team does not set a primary company on multi-company deals, set one in HubSpot first.
Deals by industry without the lookup
Try the questions on sample CRM data with no signup, then connect HubSpot and ask for deals by industry.
Try Quiriz free →