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

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

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