Index Collect or Index Match

Michelle Fayed · 02/08/22

I am trying to create a formula that will automatically collect data from a column on my master sheet. This some of these columns (on the master sheet) are automatically populated when the data brought in. For example, my status column will say Open, Closed, Task, or Canceled always.

I created the following Index Collect formula, however it keeps coming back as #INCORRECT ARUGMENT

=INDEX(COLLECT({Status}, {Service Request}, [Service Request #]@row, 1))

So I created an Index Match formula, however it is not returning data that might have populated a cell when I initially entered it into my master sheet:

=INDEX({Status}, MATCH([Service Request #]@row, {Service Request}, 1))

I would appreciate any help from the SmartSheet wizards here, and thank you in advance for your time, attention and assistance!

Kelly Moore · 02/09/22

Hey @Michelle Fayed

Your approaches are good, its the syntax that needs to be tweaked.

With only one criteria, typically INDEX/MATCH is used.

=INDEX({Status}, MATCH([Service Request #]@row, {Service Request}, 0))

The zero indicates your data is unsorted. In the bazillion Index/Matches I've written, I've always used the zero.

In the Index/Collect - which is a valid approach and typically is used with more than one criteria, you have your one and parenthesis out of place. I encourage you to track your formula using the wizard in the formula window. It shows you the syntax and if you hover in your formula, it will show you where the formula believes you are.

=INDEX(COLLECT({Status}, {Service Request}, [Service Request #]@row),1)

Does this get you to your results?

Kelly

Michelle Fayed · 02/09/22

@Kelly Moore Thank you for your response and insight.

I added the Index Collect formula to my column and it worked for the items that were already there! However, any new items that added by a workflow to copy to this sheet from my master sheet after I made it the column formula now shows the error message #INVALID VALUE.

I hope this makes sense. I am a just firefighter living in a formula world. I attached an image to show you want I am seeing. I greatly appreciate the help!!!

INVALID VALUE.png

Uploaded Feb 9, 2022167.2 KB