vlookup vs. Match (identifying new form submissions compared to a static list)

Kevin Rokus

Scenario: 

• I have a list of 200 customers that need to be surveyed by our team. 
• Some customers will receive multiple surveys, as they are served by multiple team members (our plan is to compile & analyze the results).
• Although to meet our goal, only 1 survey per customer is needed
• We are using a Smartsheet form to distribute the survey

Problem:

• As the survey results flow in, what is the best way to compare the unique values on the landing sheet (in this case, customer name, ex. ABC Corp, XYZ Inc, etc) to the static list of 200 customers (on a separate sheet)?
• In other words, we are looking to quickly identify which customers have been surveyed, and which ones are still pending
• We have a formula that is calculating the unique # of customer surveys that have come in (very handy for knowing that we have X out of 200 completed)... but the actual customer names is what we are after
• Keep in mind, our static list of customers that need to be surveyed is sorted in alphabetical order, but the survey results will be coming in in random order, and we do not want to have to go to the sheet and manually sort
• I do not have experience with vLookup, so trying to use it has not gone well

Any suggestions?

Paul Newcome

You could actually use an IF statement for this. In this example I will say we are using a column called [Surveyed?] that is a checkbox type. We will write a formula that will search your [Company Name] column in the Master Sheet (the one that the forms populate) and check the box on your Metrics Sheet (the static list) if the company name in the [Company Name] column of your Metrics Sheet is listed.

 

IF(ISTEXT(JOIN(COLLECT({Master Sheet Company Name Column}, {Master Sheet Company Name Column}, @cell = [Company Name]@row))), 1)

 

What this does is COLLECT all cells in the {Master Sheet Company Name} column that match whatever is in the [Company Name] column in your Metrics Sheet for whatever row the formula is on.

 

It will then join all of those cells that match together. If there are no cells that match, the JOIN/COLLECT will return a blank (meaning the company name isn't on the master sheet).

 

If it is blank when there are no matching cells, then it means that if there are matching cells, the JOIN/COLLECT will return a string of text (the company name repeated however many times it is found on the Master Sheet).

 

Since we don't care how many times it shows up, just that it shows up, we wrap that JOIN/COLLECT in an ISTEXT function and use that as our logical statement in our IF function.

 

So basically this formula is saying that if any text is returned by joining all cells that are collected from the master sheet range that match the company name here on the metrics sheet, then check the box.

.

We can also use:

 

=IF(FIND([Company Name]@row, JOIN({Master Sheet Company Name Column}, " - ")) > 0, 1)

 

This basically joins the ENTIRE [Company Name] column on the master sheet and separates it all out using a hyphen in one loooooong string of text. It will then search that entire string. If it finds the appropriate company name anywhere, then it will check the box.

.

There are numerous other options that will achieve the desired result, but these are the two most straightforward options that I can think of with the second actually being more straightforward than the first.

Kevin Rokus

Thanks!  I couldn't get option #1 to work (is the "{Master Sheet Company Name Column}" phrase supposed to be duplicated?)... but option #2 worked perfectly... .thank you very much!

-KR

Paul Newcome

Happy to help. For option 1, yes. The same range is repeated. Glad you were able to get something working though.