Why hlookup error when I insert table array formula?

[Narnimar]

I wanted to hlookup like this which gives me #VALUE!
=HLOOKUP(D5,"'RM LIST'!"&ADDRESS(ROW('RM LIST'!I15:I500),MATCH(BATCH!D5,'RM
LIST'!15:15,0),4)&":"&ADDRESS(ROW('RM LIST'!I500:I500),MATCH(BATCH!D5,'RM
LIST'!15:15,0),4),3,FALSE)
But if the in this formula is working.
=HLOOKUP(D5,'RM LIST'!I15:I500,3,FALSE)
Actuallly the table array "'RM LIST'!I15:I500" I replaced with my substitute
"'RM LIST'!"&ADDRESS(ROW('RM LIST'!I15:I500),MATCH(BATCH!D5,'RM
LIST'!15:15,0),4)&":"&ADDRESS(ROW('RM LIST'!I500:I500),MATCH(BATCH!D5,'RM
LIST'!15:15,0),4) . I tested this part by comparing "'RM LIST'!I15:I500"=
substitute formula which returns TRUE. But cant get the result when I insert
it in hlookup!! why?

 

[Max]

Think you need to use INDIRECT for the "derived" table array,
indicatively:
=HLOOKUP(D5,INDIRECT("derived_tablearray"),3,FALSE)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Max]

What was meant earlier was to try it like this (untested):
=HLOOKUP(D5,INDIRECT("'RM LIST'!"&ADDRESS(ROW('RM
LIST'!I15:I500),MATCH(BATCH!D5,'RM LIST'!15:15,0),4)&":"&ADDRESS(ROW('RM
LIST'!I500:I500),MATCH(BATCH!D5,'RM LIST'!15:15,0),4)),3,FALSE)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---