IF and VLOOKUP?

aleksandra.marszalek91136

Hi

 

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!

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!