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?