Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MATCH function
How can I get the functionality of the MATCH function to match a text to the
LAST match in a column. The MATCH function returns the location of the FIRST match. This example shows what I am trying to do: A B C 1 Name1 NumOfDays1 StartDate 2 Name2 NumOfDays2 3 Name3 NumOfDays3 4 Name5 NumOfDays4 5 Name1 NumOfDays5 6 Name5 NumOfDays6 7 Name1 NumOfDays7 8 Name2 NumOfDays8 9 Name5 NumOfDays9 NameN (in cloumn A) is a text and can have any value. NumOfDaysN (in column B) is an integer and have any value. StartDate(in cell C1) is a date. I need a formula to place in cells C2:C9. Foe example, the formula in cell C9 would look in cells A1:A8 and find the LAST item in the range that matches the text in cell A9, which would be cell A6 (becase both cell A9 and A6 contain Name5), than add NumOfDays9 (cell B9) to the date that would be in cell C6, and calculate a new date for cell C9. If you would be kind enough to help me even more, you could give me a formula that when it adds number of days to a date, it calculates the new date as if it were adding only work days. Another words, if a weekend falls between the old and the new date, it adds 2 extra days to calculate the new date. I really appreciate the help. I spent half a day yesterday to try to figure out how to do this but was unsuccessful. |
#2
|
|||
|
|||
You may need to double check the range references and also review
absolute/relative references: =INDEX(C1:C8,MAX((A1:A8=A9)*ROW(A1:A8)))+$B$9 This is an array formula - which must be confirmed w/ Cntrl+Shift+Enter. "David" wrote: How can I get the functionality of the MATCH function to match a text to the LAST match in a column. The MATCH function returns the location of the FIRST match. This example shows what I am trying to do: A B C 1 Name1 NumOfDays1 StartDate 2 Name2 NumOfDays2 3 Name3 NumOfDays3 4 Name5 NumOfDays4 5 Name1 NumOfDays5 6 Name5 NumOfDays6 7 Name1 NumOfDays7 8 Name2 NumOfDays8 9 Name5 NumOfDays9 NameN (in cloumn A) is a text and can have any value. NumOfDaysN (in column B) is an integer and have any value. StartDate(in cell C1) is a date. I need a formula to place in cells C2:C9. Foe example, the formula in cell C9 would look in cells A1:A8 and find the LAST item in the range that matches the text in cell A9, which would be cell A6 (becase both cell A9 and A6 contain Name5), than add NumOfDays9 (cell B9) to the date that would be in cell C6, and calculate a new date for cell C9. If you would be kind enough to help me even more, you could give me a formula that when it adds number of days to a date, it calculates the new date as if it were adding only work days. Another words, if a weekend falls between the old and the new date, it adds 2 extra days to calculate the new date. I really appreciate the help. I spent half a day yesterday to try to figure out how to do this but was unsuccessful. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
VlLOOKUP function with MATCH | Excel Worksheet Functions | |||
Match function selecting first value it matches on exactly | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |