HELP with INDEX function

[manju]

Hi There

I need help with this

Sheet 1
TL TM Mon CRT1 CRT2 CRT3
jawahar Shiva Aug 50 65 42
binu asha Aug 65 58 65
shinu vinod Aug 45 64 65

Sheet 2
TL TM Aug Sep oct
jawahar Shiva 50 65 42
Mohan 65 45 19

I have used
=INDEX(Sheet1!D$1$18,SUMPRODUCT(--(Sheet1!$A$2:$A$18=Sheet2!$A5),--(Sheet1!$B$2:$B$18=Sheet2!$B5),
(ROW(Sheet1!$A$2:$A$18))))

I dont have any problem with first row it is correct However in following
cases it is giving wrong values.

Case1: TL & TM column is blank.
Case2: I have used a name in TL & TM column which is not there in
Sheet1.(see row three under sheet2 Mohan is not there in sheet 1)

In these two cases it is giving me some values from the array since
If you set row_num or column_num to 0 (zero), INDEX returns the array of
values for the entire column or row, respectively
the value of
["SUMPRODUCT(--(Sheet1!$A$2:$A$18=Sheet2!$A5),--(Sheet1!$B$2:$B$18=Sheet2!$B5),(ROW(Sheet1!$A$2:$A$18))))"]
in case1 & Case2 is zero
That is Index(array,0)
I need excel to caution me show me notice in these cases

Please help me
Manju

 

[Herbert Seidenberg]

You can shorten your formula to
=SUMPRODUCT((TL=Sheet2!$A2)*(TM=Sheet2!$B2)*CRT1)
assuming column D = CRT1 = numbers
and TL, TM items are unique.