Return all correspondence in excel

Question

Hello, everybody!

I am developing a table that returns the activities performed by each employee of my company. The reference table is as follows::

Based on this table, I am developing individual tables for each employee, of which should show the activities that it performs, as an example below:

For this, I am using the following formula:

=SE(LINS($1:1)>CONT.SE($M$2:$M$70;$S$2);"";ÍNDICE($H$2:$H$70;MENOR(SE($M$2:$M$70=$S$2;LIN($M$2:$M$70)-LIN($M$2)+1;FALSO);LINS($1:1))))

This formula is partially working. The only problem is that it is not returning the activities that the collaborator exercises together with others. That is, it does not return activities that have more than one assigned contributor.

I tried to replace this part of the function:

SE($M$2:$M$70=$S$2;

FOR:

SE($M$2:$M$70=ÉTEXTO($S$2);

But it’s not working. Would you have any other solution? Thank you!

Answer 1

Hello!

A change in the formula that can help is to check if the cell contains the searched text, as follows:

=SE(ÉNÚM(LOCALIZAR("Texto Procurado";A3));"OK";"Não OK")

Whereas A3 would be the reference of the cell in which the text is being searched.

I hope it helps!