Find all results of an unique group with a formula in an excel report

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.

formula excel


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 ?





To know the row ID, put this formula:


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


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


  • 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:


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.

Interesting Management