INDEX COLLECT, how to collect multiple values in a range when the criteria matches

Anup

I want to know all the employees those have salaries 13 using Index formula. Presently I am using the below formula, which provides only the first Employee getting 13 salary (in the cell belonging to 3rd column & 49th row)

=INDEX(COLLECT([Column2]42:[Column2]47, [Primary Column]42:[Primary Column]47, [Column3]49), 1)

bisaacs

Hey @Anup,

You can have it collect all the Employee Names and put them in 1 cell using JOIN/COLLECT like this:

=JOIN(COLLECT([Column2]42:[Column2]47, [Primary Column]42:[Primary Column]47, [Primary Column]@row), ", ")

Is this what you're hoping to do?

Anup

Hi @bisaacs, That is wonderful, the output is depicted in a single cell, whereas I need to get the result in successive rows or in a single column.