Index Collect

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!


Answers

  • Paul Newcome
    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)

  • 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!