Collect / Index / Match

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?

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 02/05/21 Answer ✓

    Hey @Nick Allgauer

    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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 02/05/21 Answer ✓

    Hey @Nick Allgauer

    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

  • Nick Allgauer
    Nick Allgauer ✭✭✭✭

    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!