Using VLOOKUP for Approval Requests Across Sheets

GOAL: 1) Have a secure and error-free VLOOKUP on Sheet B and 2) Run a multi-level approval request automation on Sheet B.

DETAILS: I have 2 sheets. Sheet A tracks contracts. It has columns for individuals (contacts) who are designated approvers for changes on contracts. Sheet B tracks providers named on the contracts to provide service. When we want to add or remove a provider from a contract, we need to run a multi-level approval request.

I created a VLOOKUP on Sheet B to lookup the contract name linked on Sheet B and return the approvers listed for that contract row on Sheet A. When those VLOOKUP contacts are successful, a multi-level approval request automation on Sheet B automates.

ISSUE: Many users need access to see and edit Sheet A. Locking the columns to secure the VLOOKUP order is only so secure. Since VLOOKUP is based on an ordered location, removing, adding, or moving columns on Sheet A will make the Approval Requests on Sheet B inaccurate/broken.

The VLOOKUP works currently but if there are changes in column locations on Sheet A it will break the VLOOKUP or return bad data on the Approval Request Automation on Sheet B.

Any ideas on how to 1) assure I get the appropriate contacts on Sheet B and 2) feel confident my automated approval contacts don't change mid-approval request?

Is there a formulation or approach I am not thinking about beyond VLOOKUP?

Example Formula for VLOOKUP:

=VLOOKUP([Contract it applies to]@row, {ApprovalContacts}, 32, false)

~so if the contact I need is not in the 32nd column location from the contract name on Sheet A; I might receive an incorrect contact OR an error on Sheet B

~both the source column and destination column are "Contact" type

Best Answer


  • Thanks very much Paul. With some maneuvering this seems to work. I'll beta test it and move forward.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!