Using VLOOKUP for Approval Requests Across Sheets

Darin Smith

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

Paul Newcome

You may want to look into an INDEX/MATCH. It is much more robust and flexible in regards to the source data and it's location within the sheet. It references single columns at a time, so left vs right or the number of columns in between doesn't matter.

=INDEX(range to pull from, row number, optional column number)

Pulls data based on pinpointing it using the final two sections. If we reference only a single column, then the third portion will not be used.

=MATCH(value to match on, range to match in, match type)

Generates a numeric value based on where within a range the value to match on is found. If we are referencing a singe/entire column, it will provide the row number which can then be used as the row number in the INDEX function. I always suggest using 0 (zero) in the final portion of the MATCH statement as it provides for an exact match.

So we will start with the MATCH function to provide a row number...

=MATCH([Contract it applies to]@row, {Other Sheet Contract column}, 0)

Now we move to the INDEX we we start by establishing the range to pull from.

=INDEX({Other Sheet Contact Column}, row number)

We drop the MATCH into the second portion of the INDEX function, and you should have a working formula...

=INDEX({Other Sheet Contact Column}, MATCH([Contract it applies to]@row, {Other Sheet Contract column}, 0))

Paul Newcome

You may want to look into an INDEX/MATCH. It is much more robust and flexible in regards to the source data and it's location within the sheet. It references single columns at a time, so left vs right or the number of columns in between doesn't matter.

=INDEX(range to pull from, row number, optional column number)

Pulls data based on pinpointing it using the final two sections. If we reference only a single column, then the third portion will not be used.

=MATCH(value to match on, range to match in, match type)

Generates a numeric value based on where within a range the value to match on is found. If we are referencing a singe/entire column, it will provide the row number which can then be used as the row number in the INDEX function. I always suggest using 0 (zero) in the final portion of the MATCH statement as it provides for an exact match.

So we will start with the MATCH function to provide a row number...

=MATCH([Contract it applies to]@row, {Other Sheet Contract column}, 0)

Now we move to the INDEX we we start by establishing the range to pull from.

=INDEX({Other Sheet Contact Column}, row number)

We drop the MATCH into the second portion of the INDEX function, and you should have a working formula...

=INDEX({Other Sheet Contact Column}, MATCH([Contract it applies to]@row, {Other Sheet Contract column}, 0))

Darin Smith

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

Paul Newcome

Happy to help! 👍️