Collect / Index / Match

Nick Allgauer

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?

Kelly Moore

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

Kelly Moore

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

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