Possibly a Match/Index or a Vlookup Question. Conditional formatting for a lookup table.

John Teh

Hi everyone,

I'm currently having an issue trying to pull the names of the team members with the highest score in each team. I am able to pull the highest score on each team with a formula that looks something like:

=MAX(COLLECT({Smartsheet Range 2}, {Smartsheet Range 1}, "Team 1"))

=MAX(COLLECT({Smartsheet Range 2}, {Smartsheet Range 1}, "Team 2"))

=MAX(COLLECT({Smartsheet Range 2}, {Smartsheet Range 1}, "Team 3"))

However, I am trying to pull the names of these people.

For Team 1 my return should be "Sammy Voisens", Team 2 should return "Michaela Chuam", Team 3 should return "Orlando Barrio".

I've been looking into Vlookups and Match/Index formulas but I can't get them to work when making the lookup tables conditional (by team). How do I approach this? What is the solution?

Genevieve P.

Hi John,

Similar to the MAX(COLLECT, you can do this with an INDEX(COLLECT. For example:

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

In your case, since you've already pulled the value of the max points with the other formula, you can reference the cell that has that value pulled as one of the criteria above:

=INDEX(COLLECT({Name Column}, {Total Points column}, [Max Points]@row, {Team Column}, "Team Name"), 1)

Let me know if this works for you, or if you would like any further clarification.

Cheers!

Genevieve

Genevieve P.

Hi John,

Similar to the MAX(COLLECT, you can do this with an INDEX(COLLECT. For example:

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

In your case, since you've already pulled the value of the max points with the other formula, you can reference the cell that has that value pulled as one of the criteria above:

=INDEX(COLLECT({Name Column}, {Total Points column}, [Max Points]@row, {Team Column}, "Team Name"), 1)

Let me know if this works for you, or if you would like any further clarification.

Cheers!

Genevieve

John Teh

This worked! Thank you Genevieve!

Genevieve P.

Wonderful!! So glad this worked for you 🙂