# Collect / Index / Match

Options
✭✭✭✭

My brain is fried so I'm hoping someone can help. I have the following {wrong} formula:

=COLLECT({Max Headcount}, INDEX({Position}, MATCH([Primary Column]@row, {Position}, 0)))

On this sheet, I have a list of positions in the primary column. On the referenced sheet, I have the same list of positions and their max headcount. I'd like it to cross reference the position and, if the same, report the max headcount.

Easy enough, right?

• ✭✭✭✭✭✭
Options

If I understand correctly, you would like to find the Max Headcount using Position on both sheets as the match. Assuming I got it right, we don't need to use Collect since we only have one criteria (Position) to match. If there were multiple criteria, then Collect might be necessary.

Try this.

=INDEX({Max Headcount}, MATCH([Primary Column]@row, {Position}, 0))

cheers,

Kelly

• ✭✭✭✭✭✭
Options

If I understand correctly, you would like to find the Max Headcount using Position on both sheets as the match. Assuming I got it right, we don't need to use Collect since we only have one criteria (Position) to match. If there were multiple criteria, then Collect might be necessary.

Try this.

=INDEX({Max Headcount}, MATCH([Primary Column]@row, {Position}, 0))

cheers,

Kelly

• ✭✭✭✭
Options

Perfect, thank you! It's been a long week. 😀 😀 😀

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!