500 free credits, no card. Try Smalt AI →

Sales

How do I score a list of leads against my ideal customer profile in a spreadsheet

Give each ICP criterion a weight, look up points per lead with XLOOKUP, then combine them with SUMPRODUCT into one 0 to 100 fit score.

Smalt AI team ·

Put your ICP criteria in a small table with a weight for each, and a points value for every answer a lead could give (for example, industry or company size band). Look up each lead’s points with XLOOKUP, then combine them with SUMPRODUCT divided by the sum of the weights. Scale the result to 0 to 100 and sort the list by it.

Build the criteria table

Use a separate sheet, called Criteria here, and keep it to the handful of things that really separate your good customers from the rest. Score each criterion 0 to 10, and weight each 1 to 5. Common candidates are industry, company size, region and job title.

For each criterion, list every answer you expect and the points it earns. An industry table might read “Logistics, 10”, “Manufacturing, 6”, “Retail, 2”. The weights themselves go on the leads sheet, in row 1 above each criterion’s helper column (see the next sections). Points have to be plain numbers, because the next steps treat anything else as zero.

The sources cited here give no validated weights. The weights you start with are your opinion, and the last section covers how to replace them with evidence.

Look up points for each lead

On the leads sheet, row 1 holds the weights, row 2 is the header row, and the leads start in row 3. Add one helper column per criterion, placed next to the lead’s raw data. In each one, use XLOOKUP to fetch the points for that lead’s answer:

=XLOOKUP(B3, Industry_Answers, Industry_Pts, 0)

Here Industry_Answers and Industry_Pts are named ranges that you define over the answer column and the points column of the industry table on the Criteria sheet. Named ranges keep the formula readable and avoid typing a sheet name into every formula. The first argument is the lead’s value, the second is the list of answers, the third is the points to return. Microsoft documents the XLOOKUP syntax, including the optional fourth argument, if_not_found. Setting it to 0 means a lead whose industry is not in your table scores zero for that criterion instead of showing an error. Matching is exact by default, so “Logistics ” with a trailing space will not match “Logistics”.

A number such as a headcount of 120 will not be found by an exact-match lookup, because your table lists bands, not every possible value. For size bands, build a band table with each band’s lower limit and use XLOOKUP with match_mode -1, which returns the exact match or the next smaller item. Microsoft’s XLOOKUP page documents the match modes.

XLOOKUP exists only in newer versions of Excel and in Google Sheets. If yours is older, the usual workaround is INDEX combined with MATCH (general spreadsheet practice, not covered by the sources cited here).

Combine the points into one score

Each criterion’s weight sits in row 1, above its helper column, so the weights line up with the points beneath them. Then, in the score column of row 3:

=SUMPRODUCT(F3:H3, F$1:H$1) / SUM(F$1:H$1) * 10

This is the weighted-average pattern from Microsoft’s guide to averages, which shows =SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7) with price per unit as the example. Using it for lead fit is an adaptation. Because points run 0 to 10, multiplying by 10 gives a score from 0 to 100, and a lead that earns full points on every criterion scores exactly 100.

SUMPRODUCT works the same way in Google Sheets. Fill the formula down the whole list, sort descending, and pick cut-offs. For example, you might start with 80 and above as top priority, 50 to 79 as worth a look, and below 50 as parked, then adjust. Choose cut-offs by looking at where your own best customers land.

Keep fit separate from interest

A fit score says how closely a company resembles your ideal customer. It does not say whether anyone there is paying attention. HubSpot’s lead scoring documentation separates fit scores, built from properties such as region, company size and industry, from engagement scores, built from events, and caps each group (the default maximum is 100 per group). Which of those options you get depends on your HubSpot subscription tier.

You can borrow the design without HubSpot. Keep this sheet as a pure fit score, and track activity such as replies or demo requests in a different column. A perfect-fit company that has never replied and a poor-fit company that replies daily need different handling, and one blended number hides that.

Doing it with an AI coworker

If your list is in an Excel file or a Google Sheet, you could give Smalt the sheet and ask it to look up the missing company details, such as industry or headcount, to fill the gaps before you score.

You still decide the criteria, weights and cut-offs.

What to check before you rely on it

Blanks and text score zero, and nothing warns you. SUMPRODUCT treats non-numeric entries as zeros, so an “N/A” in a points column quietly lowers a lead’s score. Filter each helper column for non-numbers before you trust a ranking.

The ranges must be the same size. The same page says SUMPRODUCT returns a #VALUE error when its arrays have different dimensions, so F3:H3 needs a matching F$1:H$1, not F$1:I$1.

A lead with no industry recorded gets zero for that criterion, which looks the same as a lead in an industry you do not serve, so missing data needs checking separately from bad fit. A count of blank cells per row tells you which scores are low because of gaps.

Test the weights against what actually happened. Take your closed-won and closed-lost deals, score them with this sheet, and see whether the won deals score higher. If a criterion you weighted 5 shows no difference in win rate, lower it; if one you ignored separates the wins, add it. Redo the check as closed deals accumulate, because the first set of weights is a guess.

Smalt is an AI coworker that takes jobs like this end to end and hands back the finished file.