Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index & Match doesn't seem to work
Copied function shown below to new worksheet.
Range name was created (to match) Table layout is identical to original TABLE (part of) called Angle_Table Overhang Length 6 9 12 15 18 21 24 12 44.9 46.0 47.2 48.4 49.7 51.1 52.5 12.5 42.8 43.8 44.9 46.0 47.2 48.4 49.7 13 45.2 46.3 47.3 48.5 49.6 50.9 52.2 13.5 47.5 48.5 49.6 50.8 51.9 53.2 54.5 =INDEX(LUps_2!Angle_Table,MATCH(E6,INDEX(LUps_2!An gle_Table,,1),0),MATCH(E12,INDEX(LUps_2!Angle_Tabl e,1,),0)) I added the sheet Name (LUps_2) to make sure it would go to the correct W/S, but originally that was not required. E6 is where the Length is Specified E12 is where the Overhang Length is specified. Result should be the intersect of the two. Please advise what (simple?) thing I'm overlooking here. Unforunately, I do not understand what all the "extra" commas are all about. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index & Match doesn't seem to work
You dont need to specify the sheet name...If at all you want to specify you
need to specify the workbook name instead of sheetname...as named range is associated with the workbook and not the worksheet =INDEX(Angle_Table,MATCH(E6,INDEX(Angle_Table,,1), 0), MATCH(E12,INDEX(Angle_Table,1,),0)) 'You can use VLOOKUP() to return the same result as below =VLOOKUP(E6,Angle_Table,MATCH(E12,INDEX(Angle_Tabl e,1,),0),0) If this post helps click Yes --------------- Jacob Skaria "BEEJAY" wrote: Copied function shown below to new worksheet. Range name was created (to match) Table layout is identical to original TABLE (part of) called Angle_Table Overhang Length 6 9 12 15 18 21 24 12 44.9 46.0 47.2 48.4 49.7 51.1 52.5 12.5 42.8 43.8 44.9 46.0 47.2 48.4 49.7 13 45.2 46.3 47.3 48.5 49.6 50.9 52.2 13.5 47.5 48.5 49.6 50.8 51.9 53.2 54.5 =INDEX(LUps_2!Angle_Table,MATCH(E6,INDEX(LUps_2!An gle_Table,,1),0),MATCH(E12,INDEX(LUps_2!Angle_Tabl e,1,),0)) I added the sheet Name (LUps_2) to make sure it would go to the correct W/S, but originally that was not required. E6 is where the Length is Specified E12 is where the Overhang Length is specified. Result should be the intersect of the two. Please advise what (simple?) thing I'm overlooking here. Unforunately, I do not understand what all the "extra" commas are all about. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index & Match doesn't seem to work
Jacob:
Thank-you. I'll add your sample to my arsenal. It came up with the same "problem" (which I see now I had not indicated in my post) However, Looking at things with a fresh eye this morning, I found my two cell references were incorrect. Once corrected, the original and your method worked fine. Sometimes it pays to spend 3 or 4 hours spinning ones wheels. It helps re-inforce to check EVERYTHING when something is not working correctly. "Jacob Skaria" wrote: You dont need to specify the sheet name...If at all you want to specify you need to specify the workbook name instead of sheetname...as named range is associated with the workbook and not the worksheet =INDEX(Angle_Table,MATCH(E6,INDEX(Angle_Table,,1), 0), MATCH(E12,INDEX(Angle_Table,1,),0)) 'You can use VLOOKUP() to return the same result as below =VLOOKUP(E6,Angle_Table,MATCH(E12,INDEX(Angle_Tabl e,1,),0),0) If this post helps click Yes --------------- Jacob Skaria "BEEJAY" wrote: Copied function shown below to new worksheet. Range name was created (to match) Table layout is identical to original TABLE (part of) called Angle_Table Overhang Length 6 9 12 15 18 21 24 12 44.9 46.0 47.2 48.4 49.7 51.1 52.5 12.5 42.8 43.8 44.9 46.0 47.2 48.4 49.7 13 45.2 46.3 47.3 48.5 49.6 50.9 52.2 13.5 47.5 48.5 49.6 50.8 51.9 53.2 54.5 =INDEX(LUps_2!Angle_Table,MATCH(E6,INDEX(LUps_2!An gle_Table,,1),0),MATCH(E12,INDEX(LUps_2!Angle_Tabl e,1,),0)) I added the sheet Name (LUps_2) to make sure it would go to the correct W/S, but originally that was not required. E6 is where the Length is Specified E12 is where the Overhang Length is specified. Result should be the intersect of the two. Please advise what (simple?) thing I'm overlooking here. Unforunately, I do not understand what all the "extra" commas are all about. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX, MATCH, IF function does not work | Excel Worksheet Functions | |||
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 |