Alternative Formula??

[akinkaraman]

I need an alternative formula for cells M2, M4 and M6 because this formula is not working at Excel for iPad.

It is working at Excel for Windows but how comes it doesn't work at iPad version of Excel.

 

[Hui]

Could you please explain the logic on how the values are acquired?

 

[akinkaraman]

M2 shows where AVIs belong to, M4 shows where PERs belong to and M6 shows where DGs belong to in A1:J31 area.

Such as If A5 has DG then across DG at L6 will be shown as AA.
Yellow lines are the coordinates and the purple lines are the groups.

In this example AVI group shows E1, K2, K4
(because 2 lines under E1, K2 and K4 have AVI)

I couldn't show it just as E1, K2, K4 in a 1 cell then it is showed in seperate cells.

If you can show in 1 cell with seperation with "," then it will be great.

 

[akinkaraman]

Below formula worked.

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(10^5*(ROW($A$1:$J$31)-3)+COLUMN($A$1:$J$31))/($A$1:$J$31=$L2),COLUMNS($M$1:M1)),"R0C00000"),0),"")