IF and VLOOKUP?

I'm not sure if should I use IF and VLOOKUP or maybe COLLECT with IF formula.

What I would like to achieve:

Smartsheet A - with list of the application name and ID

Smartsheet B - with results of the evaluation, where is application name and ID as well and Repsondent Role, and Result

I would like to collect the Result from the Smartsheet B in the right cell in the smartsheet A if the Respondent Role are Application Owner, and also a same formula for 2 other types of Respondent Role.

=IF([Respondent Role]1 = "Application Owner"; VLOOKUP([Results]1; {Questionnaire 1}; 5; false)) but that formula doesn't work...

thx!

Find more posts tagged with

Comments

Andrée Starå

Hi Aleksandra,

There are a few different ways to structure a solution.

How many rows can there be in the sheets? How many columns can there be in the sheets?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

aleksandra.marszalek91136

Hi Andree,

I think is detailed enough but I will try to refrase my question/problem.

Smartsheet A - its a list of 100 rows and include 10 columns among other the Application Name, ID and Score AO, Score SU, Score U

Smartsheet B - with results of the evaluation of those Application mentioned in Smartsheet A, where is the Application Name, ID as well and also Repsondent Role and Result columns

I would like to collect:

1. Result from the Smartsheet B in the right cell (Score columns) in the smartsheet A, IF the Respondent Role = Application Owner.

2. Result from the Smartsheet B in the right cell in the smartsheet A, IF the Respondent Role = Superuser.

3. Result from the Smartsheet B in the right cell in the smartsheet A, IF the Respondent Role = User.

so the formula should go trought the Smartsheet A where should be a formula in the Score AU column where will be searching the Application Name f.eg. "OCS" then check if the Respondent Role is Application owner then place that result here.

in the Score SU column where will be searching the Application Name f.eg. "OCS" then check if the Respondent Role is Superuser then place that result here.

in the Score U column where will be searching the Application Name f.eg. "OCS" then check if the Respondent Role is User then place that result here.

Andrée Starå

I totally missed your answer!

I'd recommend using a VLOOKUP or INDEX/MATCH combination for your use case.

Would that work?

Let me know if you have any questions on how to set it up!

Sign in to join the conversation!

Quick Links

Trending discussions

Automated email with number of pending records
Hello, I use Smartsheet in such way that people are asked to provide data on many records and then that data needs to be approved by one other person (always the same person). I would like to set up an automation that, each morning, checks to see if there are records waiting to be approved, and if there are, sends an…
How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
License Question
I already have a Smartsheet license with my company, but I was recently invited to collaborate on a sheet owned by another group within the same organization. When I accepted, I was told I need to upgrade in order to edit or contribute. Can you please explain why this is required? It seems unreasonable to need multiple…