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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!