PDA

View Full Version : how to get: Index function keep value?



yurble_vn
09-07-2007, 07:43 PM
Hi All,

I use followings code to get a value which match 2 criteria. But after the source file is turn of, it return #Value!

INDEX('D:\CI\ConsumerKPI\07-07\[HCM Jul 07 KPI Tabs.xls]KPIA2021'!$A$1:$BA$3000,MATCH($C9,OFFSET('D:\CI\ConsumerKPI\07-07\[HCM Jul 07 KPI Tabs.xls]KPIA2021'!$A$1,$D9,0,73,1),0)+$D9,MATCH(E$8,'D:\CI\ConsumerKPI\07-07\[HCM Jul 07 KPI Tabs.xls]KPIA2021'!$12:$12,0))

rbrhodes
09-08-2007, 01:57 PM
Hi yurble_vn,

Trap the error:

=IF(ISERROR(INDEX( 'D:\CI\ConsumerKPI\07-07\[HCM Jul 07 KPI Tabs.xls]KPIA2021'!$A$1:$BA$3000,MATCH($C9,OFFSET('D:\CI\ConsumerKPI\07-07\[HCM Jul 07 KPI Tabs.xls]KPIA2021'!$A$1,$D9,0,73,1),0)+$D9,MATCH(E$8,'D:\CI\ConsumerKPI\07-07\[HCM Jul 07 KPI Tabs.xls]KPIA2021'!$12:$12,0))),"",INDEX( 'D:\CI\ConsumerKPI\07-07\[HCM Jul 07 KPI Tabs.xls]KPIA2021'!$A$1:$BA$3000,MATCH($C9,OFFSET('D:\CI\ConsumerKPI\07-07\[HCM Jul 07 KPI Tabs.xls]KPIA2021'!$A$1,$D9,0,73,1),0)+$D9,MATCH(E$8,'D:\CI\ConsumerKPI\07-07\[HCM Jul 07 KPI Tabs.xls]KPIA2021'!$12:$12,0)))