Welcome!

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

Kevin Rokus · 2019-04-17T13:32:36+00:00

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…

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?

Find more posts tagged with

Forms

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Use Sheet Summary as cell reference for a different sheet
I'm using salesforce connector to pull my team's hours information in real-time. The Salesforce connector sheet contains sheet summaries that I'd like to use a cell reference for a different sheet. I can't seem to find the best way or formula to do this. I don't want to use a dashboard with report widgets because I prefer…
Issues with IF formula
I have two formulas which work well independently, but when I combine them they don't. formula 1: =IF(YEAR([Joined date]@row ) = 2025, JOIN(COLLECT({Membership Survey 2025 - Experience}, {Membership Prioritisation Survey 2025 - Org}, [Organisation name]@row ))) formula 2: =IF(YEAR([Joined date]@row ) < 2025,…
Forms & automation
Hello, I use Smartsheets in a Higher Education setting. I have one sheet (Sheet A) for a list of students this sheet contains their student id numbers, email addresses, as well as other helpful information. I built a If Yes/If No check column on this sheet as well. My next sheet (sheet B) is a form that is asking is the…