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