Index Function

  • Thread starter Thread starter Gulfman100
  • Start date Start date
Why do the formulas return errors (1) when the array
name is used, and (2) when cells are pointed to
on another sheet?
Click to expand...

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)
 
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
 
Back
Top