Prev: averaging a range
Next: formula for month that is prior to actual
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" <curtis.eadie(a)yahoo.ca.(do not spam)> 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" <dguillett1(a)gmail.com> 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" <curtis.eadie(a)yahoo.ca.(do not spam)> 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 range
Next: formula for month that is prior to actual