Macro with multiple index result

[ErgoTeam]

Hello Freinds,

I need a macro will help me with multiple INDEX result and the output will be in single cell

Please note in Sheet 1, Column A have Student name and Row 1 have class number. Now in Sheet 2, I want the class number which they were not present (mentioned as A in Sheet 1) against the respective student.

[Glenn Kennedy]

Hi there.

This code (in a module)

Please Login or Register  to view this content.

and this formula:
=ConcatY(IF(B2:AE2="A",$B$1:$AE$1,""))

entered as an array, will do that.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...