Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Match
I have a spreadsheet that has a vertical list of data that is driven by dates. The same date will have many entries of different formulas and weights. The formulas or weights could be the same for multiple days. A B C 1/26/09 F123 270000 1/26/09 F852 60000 1/26/09 F331 90000 1/27/09 F456 150000 1/27/09 F331 45000 1/27/09 F876 60000 I would like to create a spreadsheet so the user can download the data above into a worksheet and based on the date selection, a formaula will return the days value of Pounds, then Formula. This is what I would like to see: A B C D 1 1/26/09 1/27/09 2 Pounds Formula Pounds Formula 3 270000 F123 150000 F456 4 60000 F852 45000 F331 5 90000 F331 60000 F876 7 8 I've tried various VLOOKUPs, and now struggling with INDEX MATCH. In each case, when I place a formula into cell A3, A4, A5 etc, I can pull in the desired data for the date in question. But when I try the same formula in C3, C4, C5 to refer to the second date, the LOOKUP still wants to begin at the begining of the selected range. The current iteration I'm trying is =IF(ISNA(INDEX((C2:C100),MATCH(1,($A$1=A2:A100)*(B 2=B2:B100),0))),"",(INDEX((C20:C100),MATCH(1,($A$1 =A2:A100)*(B2=B2:B100),0)))) Is what I want to do even possible in Excel? Do not want to go to the "dark side", and use Access. What am I missing? -- Thanks!! Don |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, Match
One formulae set up to deliver as desired:
Illustrated in this sample: http://freefilehosting.net/download/44f0m Index n match multiple results.xls Construct: Source data as posted assumed in Sheet1, in row 2 down In another sheet, Input date of interest will be in B1 In A3: =IF(Sheet1!$A2="","",IF(Sheet1!$A2=B$1,ROW(),"")) Leave A1:A2 empty In B3: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!$C:$C,SM ALL(A:A,ROWS($1:1))-1)) In C3: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!$B:$B,SM ALL(A:A,ROWS($1:1))-1)) Copy A3:C3 down to cover the max expected extent of source data in Sheet1. Minimize col A. Cols B and C returns results sought for the input date in B1. Then just copy entire cols A to C when its all dressed up/completed, paste into adjacent cols E to G, I to K, etc to "replicate" as many other similar "enquiry" ranges as desired. If the above helps, pl mark this response by pressing YES button below. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Don" wrote: I have a spreadsheet that has a vertical list of data that is driven by dates. The same date will have many entries of different formulas and weights. The formulas or weights could be the same for multiple days. A B C 1/26/09 F123 270000 1/26/09 F852 60000 1/26/09 F331 90000 1/27/09 F456 150000 1/27/09 F331 45000 1/27/09 F876 60000 I would like to create a spreadsheet so the user can download the data above into a worksheet and based on the date selection, a formula will return the days value of Pounds, then Formula. This is what I would like to see: A B C D 1 1/26/09 1/27/09 2 Pounds Formula Pounds Formula 3 270000 F123 150000 F456 4 60000 F852 45000 F331 5 90000 F331 60000 F876 7 8 I've tried various VLOOKUPs, and now struggling with INDEX MATCH. In each case, when I place a formula into cell A3, A4, A5 etc, I can pull in the desired data for the date in question. But when I try the same formula in C3, C4, C5 to refer to the second date, the LOOKUP still wants to begin at the begining of the selected range. The current iteration I'm trying is =IF(ISNA(INDEX((C2:C100),MATCH(1,($A$1=A2:A100)*(B 2=B2:B100),0))),"",(INDEX((C20:C100),MATCH(1,($A$1 =A2:A100)*(B2=B2:B100),0)))) Is what I want to do even possible in Excel? Do not want to go to the "dark side", and use Access. What am I missing? |
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 |