Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why hlookup error when I insert table array formula?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why hlookup error when I insert table array formula?
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 --- "Narnimar" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why hlookup error when I insert table array formula?
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Error | Excel Worksheet Functions | |||
Array Formula Error | Excel Worksheet Functions | |||
HLOOKUP Formula Error | Excel Worksheet Functions | |||
array formula with if and hlookup | Excel Worksheet Functions | |||
Is there any way to use HLOOKUP in an array formula to return multiple matches? | Excel Worksheet Functions |