Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and match closest
example data:
a1 b1 c1 d1 e1 f1 S-213A 10/19/2006 60. 10/20/2006 S-213A S-213A 11/3/2006 66. 1/2/2007 S-200A S-200A 11/4/2006 50. . S-200A 12/3/2006 69. . I need help with formula in f2 that would: Find S-213A in columnA, then match CLOSEST date from D2 with ColumnB, then return value in column C. So in this case f2=60 and f3=69. THANKS! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and match closest
Try this array formula**:
=INDEX(C$2:C$5,MATCH(1,(A$2:A$5=E2)*(ABS(B$2:B$5-D2)=MIN(IF(A$2:A$5=E2,ABS(B$2:B$5-D2)))),0)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mona" wrote in message ... example data: a1 b1 c1 d1 e1 f1 S-213A 10/19/2006 60. 10/20/2006 S-213A S-213A 11/3/2006 66. 1/2/2007 S-200A S-200A 11/4/2006 50. . S-200A 12/3/2006 69. . I need help with formula in f2 that would: Find S-213A in columnA, then match CLOSEST date from D2 with ColumnB, then return value in column C. So in this case f2=60 and f3=69. THANKS! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and match closest
T. Valko-
Thank you so much. This is working. I have a change. Instead of "closest" date I need the closest date but not greater than. I modified my example a bit to explain. "T. Valko" wrote: Try this array formula**: =INDEX(C$2:C$5,MATCH(1,(A$2:A$5=E2)*(ABS(B$2:B$5-D2)=MIN(IF(A$2:A$5=E2,ABS(B$2:B$5-D2)))),0)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mona" wrote in message ... example data: a1 b1 c1 d1 e1 f1 S-213A 8/19/2006 60. 10/20/2006 S-213A S-213A 11/3/2006 66. 1/2/2007 S-200A S-200A 11/4/2006 50. . S-200A 11/3/2007 69. . I need help with formula in f2 that would: Find S-213A in columnA, then match CLOSEST date from D2 with ColumnB, then return value in column C. So in this case f2=60 and f3=50. THANKS! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
index and match closest
I need the closest date but not greater than.
If there is no date that meets the condition then the formula will return a #N/A error. Try this array formula** : =INDEX(C$2:C$5,MATCH(1,--(B$2:B$5=MAX(IF((A$2:A$5=E2)*(B$2:B$5<D2),B$2:B$5) )),0)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mona" wrote in message ... T. Valko- Thank you so much. This is working. I have a change. Instead of "closest" date I need the closest date but not greater than. I modified my example a bit to explain. "T. Valko" wrote: Try this array formula**: =INDEX(C$2:C$5,MATCH(1,(A$2:A$5=E2)*(ABS(B$2:B$5-D2)=MIN(IF(A$2:A$5=E2,ABS(B$2:B$5-D2)))),0)) Copy down as needed ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mona" wrote in message ... example data: a1 b1 c1 d1 e1 f1 S-213A 8/19/2006 60. 10/20/2006 S-213A S-213A 11/3/2006 66. 1/2/2007 S-200A S-200A 11/4/2006 50. . S-200A 11/3/2007 69. . I need help with formula in f2 that would: Find S-213A in columnA, then match CLOSEST date from D2 with ColumnB, then return value in column C. So in this case f2=60 and f3=50. THANKS! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to index the closest data(cash flow)? | Excel Worksheet Functions | |||
Text comparison - closest match | Excel Discussion (Misc queries) | |||
closest match | Excel Worksheet Functions | |||
Finding Closest Match | Excel Worksheet Functions | |||
Closest number match help ... | Excel Worksheet Functions |