Here's some key explanations ..

Typical core array expression:
MATCH(1,(Main!$B$6:$B$15=D6)*(Main!$E$6:$E$15=$E$3 ),0))

The lookup_array part: (Main!$B$6:$B$15=D6)*(Main!$E$6:$E$15=$E$3)
is essentially a product of 2 conditions: = Cond1 x Cond2
which will produce a resultant array of zeros with a single 1,
eg:{1;0;0;0;0;0;0;0;0;0}
where the "1" indicates the row position satifying both conditions (Cond1 &
Cond2)

[Cond1: where Reg# criteria satisfies, Cond2: where subject code criteria
satisfies,
Cond1 x Cond2: where both criteria satisfy]

MATCH(1,{1;0;0;0;0;0;0;0;0;0},0))
then matches the "1" to the array above to return the row position within
the array

INDEX(Main!$F$6:$F$15,MATCH(...))
then returns the contents of the cell within F6:F15 corresponding to the row
position returned by MATCH

The nested:
=IF(ISNA(MATCH(1),IF(ISNA(MATCH(2),...
INDEX(...,MATCH(2))),INDEX(...,MATCH(1)))
will check through in sequence, the possibility of any inputs for the
subject codes: 1.1, 1.2, etc being made within either of cols E,G,I,K
and if so, the corresp INDEX(...,MATCH(..)) will then return the required
result from either of cols F,H,J,L

The implicit assumption of course, is that there will not be any duplicate
subject code inputs.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"starguy" wrote:

Infact it will take some time to implement this formula in my original
workbook that contains large data and references are also not the same
as were in the sample file.
I am also working on another formula for the same purpose and that is
not an array. I will share it with you after I get success to complete
that formula.


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813