Index Collect

Karlagarf

Hi everyone.

I need to build a sheet based on an approval matrix per function/dep code and differents amounts.

I figured out how to pull data from a cell using Index Match formula to bring the Approver on my target sheet based on the cell "Function/dep code" but now I need to bring the approver based on the amount input in column PO amount. Depending on the amount it would need approval from different people.

I tried using index collect but I kindly ask for help as I do not know how to build it

thanks!

Paul Newcome

INDEX/COLLECT syntax is actually really straight forward.

Build the COLLECT first.

COLLECT({Range To Pull From}, {1st Criteria Range}, 1st Criteria, {2nd Criteria Range}, 2nd Criteria, {3rd Criteria Range}, 3rd Criteria)

You can keep adding range/criteria sets until you have everything covered.

Now that you have COLLECTed all of the matching rows, you want to INDEX that first one.

=INDEX(COLLECT(.........), 1)

Karlagarf

thanks, Paul, very helpful. although I did struggle a little bit. still think that i need to improve the way i figured this out, (but at least it is working)

here's what I did

I made that work by (i) having an approval matrix sheet, in one column the department, another column for the approval level, eg: less than 100K USD, over 250K USD, then a 3rd column with the name (email) of the approver.

on my target sheet, I added a helper column referencing the amount column with a simple IF formula for the amount, eg: :IF(amount<100000,"less than 100K USD" ....and added the other criteria I needed)...

any suggestions are welcome.