Find all row numbers from a table matching a value

[jb]

Hello helpers,

I have a worksheet with a table from B1:F42. This table is actually receiving values dynamically in original sheet. So the names in given table keeps on changing everyday.

Column A indicates numbers 1 to 7. This numbers are work slot numbers. They are fixed.

Table B45:E52 is testing table where I have applied formula.
B5 to B52 indicates list of employees. I want to find out all row numbers where an employee name appears.

I have tried it for ANDY. I have written array formula in cells D45 to D51. I have used it in 7 rows because maximum his name can appear 7 times.

Name of ANDY appears in data table 4 times in row number 13,19,31,37. My formula is working perfectly. But for last 3 row it gives #NUM! message because there are only 4 occurances of ANDY.

I have tried to drag formula in cell E45 to have this output in horizontal format. But it is not working.

My ouptut requirement is shown in table B55:I62. I want output in horizontal format.
I have created table for 7 columns C to I because there are maximum 7 slots.

Also a small care is to taken. The employees whose names are required to be searched i.e. b55 to b62, are dynamically generated in original sheet based on condition.

So, names are also changed daily. Sometimes no empolyess are to be searched, sometimes 2 or 5 etc.

So formula should be such that it can be applied on that dynamic list.

Help required.

 

[Nebu]

Hi:

Formulas in Coloured cells

Thanks

 

[p45cal]

In cell C55, array-entrered (Ctrl+Shift+Enter):
=IFERROR(SMALL(IF($B$1:$F$42=$B45,ROW($B$1:$F$42)),COLUMN()-2),"")
copy down and across.

 

[Khalid NGO]

Hi,

Or this with just Enter:

=IFERROR(AGGREGATE(15,6,1/(($B$1:$F$42=$B45)/ROW($B$1:$F$42)),COLUMNS($A1:A1)),"")

Regards,