In one of my many reports, I needed to display all data of a same value, for example, for a particular team group, I needed to find all its corresponding activities.

When I use the formula ?

To display all the results of an unique value.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

For the formula to work properly, I need to do an additional action, by pressing “shift + control + enter” in order to put the formula between brackets. If not, it will show an error.

How are the formulas ?

=SMALL()

=IF()

=ROW()

=INDEX()

To know the row ID, put this formula:

=SMALL(IF(A:A=D2,ROW(A:A)),ROW(1:1))

Then pressing “shift + control + enter” to put it between brackets so I will get the result and not an error:

{=SMALL(IF(A:A=D2,ROW(A:A)),ROW(1:1))}

After that, just copy it down to the other cells and the formula will be updated automatically.

Explanation:

• D2 is where I put the group name to get the result I want
• A:A is the column letter where it is looking for the group
• 1:1 is the row number where it is starting to look for

The result is on the column D.

To display the corresponding results:

=INDEX(A:B,SMALL(IF(A:A=D2,ROW(A:A)),ROW(1:1)),2)

As for the other, press “shift + control + enter” and the result is on the column E. To display the name of the category, I need to reference the column B with the number 2 at the end of the formula.