VLOOKUP/Matching/Collecting

twa10028

Hi there,

When a prospective customer fills out an online form and provides us with information, I want to be able to automatically match the specified services to one of our advisors from a separate database I have built.

I understand VLOOKUP for one search value from one lookup table, but how can I specify multiple search values.

For example, if a client enters 3 criteria by filling out a form, how can I use Smartsheet to assign one of my advisors automatically?

1. Type

2. Region,

3. Required Service

 

=VLOOKUP(Type1, Region1, Required Service1, {Advisor Database Range 1}, 3, false)

The formula above is clearly giving me issues.  What is the best way to do this?

Is there also a way to have the leads assigned based on priority?

 

Thank you!

Paul Newcome

You could use something along the line of =JOIN(COLLECT(................))

This would give you the ability to JOIN all of the name cells on the other sheet that match the specific criteria set forth in the COLLECT function. Assuming there is only one name that matches each of the different sets of requirements, it will only show one name.

 

So basically if only one person will handle Type 1, Region 1, Required Service 1, then anytime that specific combination is filled out in the form, that one name will be returned by the formula. If multiple people handle that specific combination, you could include a delimiter in after the COLLECT function to make it easier to read. Something like...

 

=JOIN(COLLECT({Cross Sheet Name Range}, {Cross Sheet Type Range}, Type@row, {Cross Sheet Region Range}, Region@row, {Cross Sheet Required Service Range}, [Required Service]@row), " - ")

Lets just say John Smith handles the specific set of criteria the prospective customer listed in the form. The formula above with display

.

John Smith

.

If both John Smith and Jane Doe handle it, the above formula would display

 

John Smith - Jane Doe

.

.

Does this help any?

twa10028

Hi Paul,

Yes this does help and it's close to what I'm looking for, but I'd like only 1 name to be returned (even if more than 1 person handles the requests).  

 

For example, let's say John Doe has a 95% satisfaction rate and Jane Doe has a 85% satisfaction rate, how would I be able to factor that into the formula so that only 1 name gets assigned as the main lead - in this case John Doe!

Hope this makes sense!  Thanks in advance

 

Paul Newcome

There are a couple of ways to do this. Basically you would use a MAX(COLLECT(..........)) function with the same criteria as the above JOIN(COLLECT(..........)). This will display the highest rating among ALL advisers for that particular set.

=MAX(COLLECT({Cross Sheet Rating Range}, {Cross Sheet Type Range}, Type@row, {Cross Sheet Region Range}, Region@row, {Cross Sheet Required Service Range}, [Required Service]@row))

.

You could either put that in a separate cell in the same sheet or in the master sheet and reference that cell like so (Same sheet for this example).

=JOIN(COLLECT({Cross Sheet Name Range}, {Cross Sheet Type Range}, Type@row, {Cross Sheet Region Range}, Region@row, {Cross Sheet Required Service Range}, [Required Service]@row, {Cross Sheet Rating Range}, @cell = [Rating Helper  Column]@row), " - ")

or build it into the actual JOIN/COLLECT

=JOIN(COLLECT({Cross Sheet Name Range}, {Cross Sheet Type Range}, Type@row, {Cross Sheet Region Range}, Region@row, {Cross Sheet Required Service Range}, [Required Service]@row, {Cross Sheet Rating Range}, @cell = MAX(COLLECT({Cross Sheet Rating Range}, {Cross Sheet Type Range}, Type@row, {Cross Sheet Region Range}, Region@row, {Cross Sheet Required Service Range}, [Required Service]@row))), " - ")

.

Unfortunately there is still the chance of it pulling more than one name if 2 of them fit all of the criteria including having the same rating. From there another extra step would be to use a basic COUNTIFS function to count how many times each person has helped and then use that additional criteria to assign it to whomever has the lower or higher count. If that is the same, you could look at dates and determine/factor in who got the last one.

This could get built out to take into account NUMEROUS factors (as you can see), but in the end, theoretically there could still always be that chance of having two names populate.

To help with that, you could use a helper flag column and use something like

=IF(FIND("-", [Assigned to]@row) > 0, 1)

This will throw a flag in your helper column if it finds the "-" symbol (the delimiter between multiple names). You can then build conditional formatting and alerts/actions to bring those (hopefully) rare occurrences to your attention with the regular day to day side of things being automated.

L_123

Index(collect(),1)

twa10028

I've attached a screenshot to show you what I'm looking to do.  The section at the top is what would come in from the form, the section at the bottom is pulled from a separate sheet (from example).

I'm having trouble with the formula and think it's because I'm only able to reference the Service from the online form and not the region/type...

Thanks in advance.

L_123

I would split this into 2 sheets. Your assignment sheet, and your scheduling sheet. The formula for the assigning sheet would be:

 

=index(collect(Technician:Technician,[Required Service]:[Required Service],[Required Service]@row,Region:Region,Region@row,Type:Type,Type@row),1)

 

Replace the column references (Bold) with their respective references on the other sheet.

 

Also In general it's much easier to submit forms to the bottom of the sheet rather than the top for the purpose of writing formulas.

Paul Newcome

Ok. So if the sheet that is populated by the form is where you are wanting to populate the name, then you would put something along the line of the following in the Technician Column...

 

=JOIN(COLLECT({Cross Sheet Reference Technician Name Column}, {Cross Sheet Reference Services Column}, [Required Service]@row, {Cross Sheet Reference Region Serviced Column}, Region@row, Cross Sheet Reference Type of Service Column}, Type@row), " - ")

.

This would pull both Jack and Joe Smith (displayed as Jack Smith - Joe Smith), but that is where you would use some of the other options listed above whether it be additional criteria and/or conditional formatting.