Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
index and match
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
index and match
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |