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