# formula building up lead scorecards

Options
edited 08/01/22

Dear all,

I have a sheet "lead management" with columns like "number employees", "sector", "region" and many others - all of them as dropdown lists

Now, I want to create another sheet with the score criteria and score values f.e.

column "number employees" // column "region"

zero employees = score value "-10 // region north = value "10"

1-5 employees = score value "0" // region west = value "5"

6-10 employees = score value "+5" // region east = value "0"

11-50 employees = score value "+10" // region south = value "-5"

what should be a formula to catch the values and combine them with the defined scores?

should I build some helper columns such as "region helper" ?

Hope I did express my ideas comprehensively ?

kind regards Joerg

• ✭✭✭✭✭✭
Options

What are the actual dropdown options? Are you able to provide a screenshot of some sample data for reference? Are you wanting to add or multiply?

• Options

Hi Paul,

thanks for taking this challenge :-)

I put two examples "number of employees" and "customer type" (instead of region)

You may assign the values to the examples as you want.

It is only the formula I am struggling with - as always :-)

Now, I am curious to see what it will be...

Wouldn´t it be better to "catch" the results of the formula either inside the same or a new sheet - lets say "lead scorecard" ?

• ✭✭✭✭✭✭
Options

I hope you're well and safe!

Here's one way to solve it in the same sheet. (I'd recommend keeping it in the same sheet if it's only this sheet that would use the Score Table)

Try something like this.

Create the Score Table.

Here's the formula.

```=
INDEX([Number of Employees (Score)]:[Number of Employees (Score)],
MATCH([Number of Employees]@row, [Number of Employees (Table)]:[Number of Employees (Table)], 0))
+ INDEX([Customer Type (Score)]:[Customer Type (Score)],
MATCH([Customer Type]@row, [Customer Type (Table)]:[Customer Type (Table)], 0))
```

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Hi Andree,

you again :-) Thanks for helping

I tried this in first step and at least there is no default "unparseable" - cell is simply blank without any result.

=INDEX([Kundenart (Score)]:[Kundenart (Score)]; MATCH(Kundenart@row; Kundenart:Kundenart; 0))

Both columns "Kundenart" and "Kundenart (Score) are dropdown list columns.

In the result column "score value" I have "text/number as column type.

The three columns are not next to each other but in the same sheet - no problem, I guess

Any idea ?🤔

• ✭✭✭✭✭✭
Options

Me again! Haha!

Happy to help!

Can you share a screenshot or the sheet?

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options
• ✭✭✭✭✭✭
Options

Have you added the Score table as in my screenshot or similar?

It seems that the score is empty.

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Why is almost everything so easy with Smartsheet ?? :-)

I did not understand on first sight that I simply can put a table inside my sheet.

Thank you so much for helping again.

• ✭✭✭✭✭✭
Options

Haha!

You're more than welcome!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Hi Andree,

one more question concerning this issue. Is it also possible to collect values from a multi dropdown as following

• Options

• ✭✭✭✭✭✭
Options

What exactly is it you are trying to have output?

• Options

Hi Paul,

I decided to put the lead criteria in another new sheet called "Leadkriterien"

In the original sheet "lead management" I make the entries for every lead or customer such as "customer type", "marketing actions", "customer relevance" and so on. I add a value to every entry. Now, I try to have the formula over these two sheets in order to have a score value

Two questions are left:

1) how to change the formular to get a correct value

2) is it possible to take values from a multi dropdown list?

=INDEX({Leadkriterien Bereich 1}[Kundenart (Score)]:[Kundenart (Score)]; MATCH(Kundenart@row; {Leadkriterien Bereich 1}[Kundenart (Übersicht)]:[Kundenart (Übersicht)]; 0)) + INDEX({Leadkriterien Bereich 2}[Marketingmaßnahme (Scorewert)]:[Marketingmaßnahme (Scorewert)]; MATCH(Marketingmaßnahme@row; {Leadkriterien Bereich 2}[Marketingmaßnahme (Übersicht)]:[Marketingmaßnahme (Übersicht)]; 0))

• ✭✭✭✭✭✭
Options

Meetings, but I'd be happy to take a look now.

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

How would you want this to work?

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!