I require assistance with multiple criteria VLOOKUP from a reference sheet

Ambika Sujir

Hello,

I require assistance with multiple criteria VLOOKUP from a reference sheet.

Here is an excerpt from my table(s)

Sheet1: Contains SolArea2, Region, HQRegion. The combination of all these 3 fields should populate a name that is maintained in another sheet (Sheet2)

<>_Approver = SolArea2 + Region + HQRegion

I need H_Approver, L_Approver and E_Approver in 3 separate columns as there as downstream approval workflows.

Sheet2: Contains the same combinations and the Approver column holds the value which should be populated in 'Hiring Manager', 'Learning Manager' and 'Engagement Manager' of Sheet1.

I'm able to achieve this using IF logic, but I think it will be easier maintenance if done in a separate table that can be referenced.

Any inputs are appreciated.

Thanks,

Ambika

Ambika Sujir

Hi @Andrée Starå ,

Thank you so much for the quick response.

This is amazing! I did not think of this possibility. Its a bit of an overstretch do do the basic copy/paste task, but at least it's an option.

Thank you so much for sharing this. I will try this out and really hope it works with my use case.

Thanks,

Ambika

Ambika Sujir

I was able to create a helper column in both sheets to lookup and populate the value. So I was able to solve my initial question.

My next question is, if in future I change the Approver Name in sheet2, then all the cell values in sheet one get updated, even the old forms that have been closed which can trigger a notification to the approver. How can I avoid this from happening?

Thanks,

Ambika

Bassam Khalil

Hi @Ambika Sujir

Hope you are fine, you can use a workflow to copy or move the completed row to final result sheet which will be used as an archive, in this way you will avoid any unwanted changes.

Ambika Sujir

Hi @Bassam Khalil ,

Hope you are well!

Thank you for sharing your inputs.

Instead of copying entire row over as an archive, is there a way I can copy values in the cell from <>Approver into a new column in the same sheet so I have reference? In that way, if the name changes in future, the formula will update it only on the correct column and not the new column I created?

Thanks,

Ambika

Andrée Starå

Hi @Ambika Sujir

I hope you're well and safe!

Please have a look at my post below with a method I developed.

More info: 

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Ambika Sujir

Hi @Andrée Starå ,

Thank you so much for the quick response.

This is amazing! I did not think of this possibility. Its a bit of an overstretch do do the basic copy/paste task, but at least it's an option.

Thank you so much for sharing this. I will try this out and really hope it works with my use case.

Thanks,

Ambika