Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Index Function
I;m sorry abouth the repetition. I am new at trying to post a file that can
be shared with others. I beleive this will work: http://www.mediafire.com/?trf3x2zin04 Thanks |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Index Function
Why do the formulas return errors (1) when the array
name is used, and (2) when cells are pointed to on another sheet? The #REF! error is being caused by the fact that the formula is in the same column as the first number in the named range _2x12. This is called the implicit intersection. So, it's using the 125 as the row_num argument. Since there aren't 125 rows in LumberDB it causes the #REF! error. The #VALUE! error is being caused by the reference to the other sheet resovling to a TEXT string "_2x12" and not the defined name that you think it is. Either way, it would not work since _2x12 is a range reference to an array and not an expression that resolves to a row number that can be used by INDEX. Try it like this: =INDEX(LumberDB,MATCH("2x12",A3:A7,0),10) Note that "2x12" is a TEXT string and not the named range. Or, name A3:A7 = Size A100 = 2x12 B100 = 10 =INDEX(LumberDB,MATCH(A100,Size,0),B100) -- Biff Microsoft Excel MVP "Gulfman100" wrote in message ... I;m sorry abouth the repetition. I am new at trying to post a file that can be shared with others. I beleive this will work: http://www.mediafire.com/?trf3x2zin04 Thanks |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Index Function
Recollect I offered you this well illustrated response earlier, based on
*your* sample, which unfortunately you seem to ignore ------------------------------------------------------ Another option to return the intersection value within the table is to use a normal index/match on top row/leftmost col Illustrated in new sheet: x in your sample: http://www.freefilehosting.net/download/3efdj Index_match top row_left col.xls In sheet: x, Assume Size & Length values are selected from DVs / input in P2:Q2 down In R2: =IF(COUNTA(P2:Q2)<2,"",INDEX($B$3:$N$7,MATCH(P2,$A $3:$A$7,0),MATCH(Q2,$B$2:$N$2,0))) Copy down to return results from the table -------------------- -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX function | Excel Discussion (Misc queries) | |||
Index Function | Excel Worksheet Functions | |||
Index function? | Excel Worksheet Functions | |||
Index Function/Match Function | Excel Discussion (Misc queries) | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |