index and match

INDEX(AD11&"_MSRP",MATCH(H11,AD11&"_Length"),MATCH(G11,AD11&"_Width"))

INDEX(Data!$D$206:$AB$230,MATCH(H11,Data!$D$205:$D$230),MATCH(G11,Data!$D$204:$AB$204,1))

the above 2 formulas refer to the same table.... and are the same except
that I've allowed for a variable in cell AD11 which changes the defined table
to be referred to. The bottom formula works fine... the top does not as it
returns "#Value". It appears as though when using the defined names, the
quotation marks it adds when referring to the ranges somehow throws off the
result. WHY!!!??? This is used for looking up prices (&"_MSRP") on a cross
reference (width (&"Width")x length (&"_Length")) price list that changes
according to the brand/product selected in a previous cell/drop down list.

if this is a range then on the spreadsheet wrap it in an INDIRECT function
so this
MATCH(H11,AD11&"_Length")
becomes
MATCH(H11, INDIRECT(AD11 & "_Length") )

If you use VBA ( and since this is a program group!)
MATCH(H11,AD11&"_Length")
becomes
MATCH(Range("H11"), Range("AD11" &"_Length") )


"MJH45040" wrote:

> INDEX(AD11&"_MSRP",MATCH(H11,AD11&"_Length"),MATCH(G11,AD11&"_Width"))
>
> INDEX(Data!$D$206:$AB$230,MATCH(H11,Data!$D$205:$D$230),MATCH(G11,Data!$D$204:$AB$204,1))
>
> the above 2 formulas refer to the same table.... and are the same except
> that I've allowed for a variable in cell AD11 which changes the defined table
> to be referred to. The bottom formula works fine... the top does not as it
> returns "#Value". It appears as though when using the defined names, the
> quotation marks it adds when referring to the ranges somehow throws off the
> result. WHY!!!??? This is used for looking up prices (&"_MSRP") on a cross
> reference (width (&"Width")x length (&"_Length")) price list that changes
> according to the brand/product selected in a previous cell/drop down list.

Yes the 'INDIRECT' works THANK YOU... buuuut, now when I enter measurements
into the width and length cells, when the width or length matches one of the
row or column headings, the formula returns one row or column off, depending
on which measurement (width or length) matches the row or column heading.
I've tried using '0', or '-1', and those return "#N/A"..... blank or '1',
works fine so long as the width or length do not equal the column or row
numbers.... now what? : )
Marty

"Patrick Molloy" wrote:

> if this is a range then on the spreadsheet wrap it in an INDIRECT function
> so this
> MATCH(H11,AD11&"_Length")
> becomes
> MATCH(H11, INDIRECT(AD11 & "_Length") )
>
> If you use VBA ( and since this is a program group!)
> MATCH(H11,AD11&"_Length")
> becomes
> MATCH(Range("H11"), Range("AD11" &"_Length") )
>
>
> "MJH45040" wrote:
>
> > INDEX(AD11&"_MSRP",MATCH(H11,AD11&"_Length"),MATCH(G11,AD11&"_Width"))
> >
> > INDEX(Data!$D$206:$AB$230,MATCH(H11,Data!$D$205:$D$230),MATCH(G11,Data!$D$204:$AB$204,1))
> >
> > the above 2 formulas refer to the same table.... and are the same except
> > that I've allowed for a variable in cell AD11 which changes the defined table
> > to be referred to. The bottom formula works fine... the top does not as it
> > returns "#Value". It appears as though when using the defined names, the
> > quotation marks it adds when referring to the ranges somehow throws off the
> > result. WHY!!!??? This is used for looking up prices (&"_MSRP") on a cross
> > reference (width (&"Width")x length (&"_Length")) price list that changes
> > according to the brand/product selected in a previous cell/drop down list.