I am working on a spreadsheet that seeks to collate scores to a team. There is an input table where team and score start next to each other (see the example sheet linked below) into a table with each teams (multiple) score next to it.

What is the best method to lookup for the second and later time a team appears in the raw score, since VLookup only returns one of the values from the list? Or barring a solution to that, is there an easier method to take all values that are associated with another and form a new table?

Here is the link: Score Lookup


You can use filters.

Use filter on the scores column and it will show you the table where scores are filtered.

Following link gives more details on using filters.


| improve this answer | |

I've added formulas into your sheet in B2:B8. They take this form:


FILTER looks at your range of scores and only includes ("filters in") cells that are not blank and where the corresponding cell to the left is equal to the team shown in the current sheet, cell A2.

Alone, FILTER would produce a vertical array. We then use TRANSPOSE to make it a horizontal array instead to fill your chart.

I created the formula in B2 and then just dragged down as far as needed.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.