how to get: Index function keep value?

[yurble_vn]

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!

[VBA]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))[/VBA]

[rbrhodes]

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)))