Why hlookup error when I insert table array formula?

N

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?
 
M

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top