Converting an Authorization Matrix into an Approval Sequence

Colin Herrera

This is tough to explain.

This first screenshot is a list of people who can authorize purchases. The 2nd column denotes their dollar value limit for any single purchase authorization. "Department" denotes what department in which they're allowed to make those authorizations.

I'm trying to convert this list to a second sheet, which will provide a sequence based on the above list.

Essentially, if someone requests to purchase an item that costs < $10,000 in Department 7090, Person 38 needs to authorize it. However, if someone requests to purchase an item that costs in department 7090, Person 38 first needs to authorize it, followed by Person 32 since the limit is above Person 38.

If the item is greater than Person 32's limit in 7090, then it goes to another tier above them. And so on until we reach a person who can authorize the limit. The sequencing part is built, but I can't figure out an easy way to populate the names from the above list into the sequencing.

Is there a formula that can make this happen?

I'm thinking in the Approver 1 column of the 2nd sheet, the formula would be something like:

=IF(MATCH({Department}, [Department]@row, 0)=1, INDEX({Person}, ...

But of course, I only want it to look at a range where the range of departments from the 1st sheet is limited to the rows that contain the [Department]@row on the 2nd sheet.

I feel like nesting a CONTAINS, HAS, or COLLECT formula in my MATCH is the solution but I can't figure it out.

Let's assume the Department column is cleaned up so that Person 32's 7079-7083 is revised to be 7079, 7080, 7081, 7082.

Devin Lee

Hey @Colin Herrera,

Placing the Names

Great question. I would approach the approver limit as a fixed variable so it breaks down into dollar columns rather than approval order. For example $10,000, $25,000, and $75,000. So Approver 1 would be $10,000, Approver 2 would be $25,000 and Approver 3 would be $75,000.

This would make it easier to lookup values because you already know what dollar value you are looking for and can use the formulas below.

[$10,000]1

=IFERROR(INDEX(COLLECT(Person:Person, Department:Department, CONTAINS([Department1]@row, @cell), [Max Limit]:[Max Limit], =10000), 1), "")

[$25,000]1

=IFERROR(INDEX(COLLECT(Person:Person, Department:Department, CONTAINS([Department1]@row, @cell), [Max Limit]:[Max Limit], =25000), 1), "")

[$75,000]1

=IFERROR(INDEX(COLLECT(Person:Person, Department:Department, CONTAINS([Department1]@row, @cell), [Max Limit]:[Max Limit], =75000), 1), "")

Order the Names

If you still want to have a list of Approver 1,2,3, etc. You can add some helper columns named Order 1,2,3 etc and use the formula below in Order 1 to join all the names across the dollar columns.

[Order 1[1

=JOIN(COLLECT([$10,000]@row:[$75,000]@row, [$10,000]@row:[$75,000]@row, NOT(ISBLANK(@cell))), "_") + "_"

In Order 2 and onward you can use the formula below. This will remove one of the names from the referenced cell. Have each column reference the previous Order column so a name is removed each time.

[Order 2]1

=IF(RIGHT([Order 1]@row, LEN([Order 1]@row) - FIND("_", [Order 1]@row)) = [Order 1]@row, "", RIGHT([Order 1]@row, LEN([Order 1]@row) - FIND("_", [Order 1]@row)))

[Order 3[1

=IF(RIGHT([Order 2]@row, LEN([Order 2]@row) - FIND("_", [Order 2]@row)) = [Order 2]@row, "", RIGHT([Order 2]@row, LEN([Order 2]@row) - FIND("_", [Order 2]@row)))

Place into Approver Order

Now we want to pull in the first name listed in each Order column so we use the formula below

[Approver 1]1

=IF([Order 1]@row = "", "", LEFT([Order 1]@row, FIND("_", [Order 1]@row) - 1))

[Approver 2]1

=IF([Order 2]@row = "", "", LEFT([Order 2]@row, FIND("_", [Order 2]@row) - 1))

[Approver 3]1

=IF([Order 3]@row = "", "", LEFT([Order 3]@row, FIND("_", [Order 3]@row) - 1))

Note

When referencing the original data check to see the cells are not number values but are strings. This might happen when there is only one set of numbers listed. Put an apostrophe at the start of the number so it will read as a string. Example: 7081 would be changed to '7081. The apostrophe will not show unless you are in the cell. Generally, you can tell by the fact that numbers are right justified while strings are left but this can be overwritten and will cause errors with some functions. In this case it's the CONTAINS function.