From: Curtis on 4 May 2010 15:33 In the source sheet COL AB = recent cost COL W = latest purchase date (format mm/dd/yyyy) COL A = Part number I need to identify the latest cost of each part number based on the criteria above. The price can change in the month so I need the most recent price as determined in Column W thanks -- ce From: Don Guillett on 4 May 2010 15:51 If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Curtis" wrote in message news:D73346FF-2662-4827-B616-A7B946EC833E(a)microsoft.com...> > In the source sheet > COL AB = recent cost > COL W = latest purchase date (format mm/dd/yyyy) > COL A = Part number > > I need to identify the latest cost of each part number based on the > criteria > above. The price can change in the month so I need the most recent price > as > determined in Column W > > thanks > -- > ce From: Glenn on 4 May 2010 15:58 Curtis wrote:> In the source sheet > COL AB = recent cost > COL W = latest purchase date (format mm/dd/yyyy) > COL A = Part number > > I need to identify the latest cost of each part number based on the criteria > above. The price can change in the month so I need the most recent price as > determined in Column W > > thanks The following array formula (commit with CTRL+SHIFT+ENTER) assumes that you have the part number you wish to look up in AC2: =INDEX(AB:AB,MAX(IF((A2:A100=AC2)* (W2:W100=MAX(IF(A2:A100=AC2,W2:W100,""))), ROW(A2:A100),""))) From: Don Guillett on 6 May 2010 16:59 =IF(\$A10="","",INDEX(colD,MAX(IF((colG=\$A10)*(colB=K\$8),ROW(colI)-5)))) defined names for the block starting at row 6 -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Don Guillett" wrote in message news:OuwDEN86KHA.6052(a)TK2MSFTNGP02.phx.gbl...> If desired, send your file to my address below. I will only look if: > 1. You send a copy of this message on an inserted sheet > 2. You give me the newsgroup and the subject line > 3. You send a clear explanation of what you want > 4. You send before/after examples and expected results. > > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett(a)gmail.com > "Curtis" wrote in message > news:D73346FF-2662-4827-B616-A7B946EC833E(a)microsoft.com... >> >> In the source sheet >> COL AB = recent cost >> COL W = latest purchase date (format mm/dd/yyyy) >> COL A = Part number >> >> I need to identify the latest cost of each part number based on the >> criteria >> above. The price can change in the month so I need the most recent price >> as >> determined in Column W >> >> thanks >> -- >> ce >  |  Pages: 1 Prev: averaging a rangeNext: formula for month that is prior to actual