Collect / Index / Match

Question

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 · Accepted 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