Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP with INDEX function
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:D$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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HELP with INDEX function
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index function | Excel Worksheet Functions | |||
Index Function/Match Function | Excel Discussion (Misc queries) | |||
Index function | Excel Worksheet Functions | |||
Index function help | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |