Which Index formula should I use to cross reference multiple columns?

mhe_sw2023

I have a sheet that includes 5-6 columns of information that I want to link to a new sheet that people will work from. The information in these columns will be updated in the original sheet so I would like the cells to be linked instead of copied. In the original sheet, the information repeats so I only want the references to pull one row of information based on two rows (a parent and child row). I can make a report that pulls what I want, but I can't use the report to feed into the new sheet. I have tried using different formulas to pull the information needed, but it's not working as expected. I was able to get the first two instances of the collection type and article title to report to the new sheet with the formula below, but the next instances do not get pulled. The columns that need to be pulled are a mix of text/number and date columns.

=INDEX(COLLECT({cdp_collectiontype}, {cdp_task}, "Image Due"), 1)

MichaelTCA

Hello @mhe_sw2023

If you are only using 1 criteria, like your example of INDEX/COLLECT, then you will only need an INDEX/MATCH function.

In your case, INDEX({cdp_collectiontype},MATCH("Image Due",{cdp_task},0)) does the exact same thing.

INDEX/COLLECT is better used when multiple criteria are involved.

Here's a guess based on what you have given:

I am guessing you are looking for a list of data that comprises of everything involved with that criteria. There's no functions in Smartsheet to create a filtered list. You can use a single criteria to pull a single value or you can use multiple criteria to pull a single value.

If my assumption is correct and you are trying to make a list, use a report and filter it.