On the face of it, think you could try an index n match,
like this, array-entered*, then copied down:
=INDEX(F$2:F$5050,MATCH(MAX(IF(A$2:A$5050=A3,C$2:C $5050)),IF(A$2:A$5050=A3,C$2:C$5050),0))
*press CTRL+SHIFT+ENTER to confirm the formula
And if you need to include an additional criteria based on what's in col D,
eg: Actual, then try something like this, array-entered:
=INDEX(F$2:F$5050,MATCH(MAX(IF((A$2:A$5050=A3)*(D$ 2:D$5050="Actual"),C$2:C$5050)),IF((A$2:A$5050=A3) *(D$2:D$5050="Actual"),C$2:C$5050),0))
Likewise for "Estimated"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"B~O~B" wrote in message
...
I have a spread sheet with 5000 lines.
Column A has 1000 different part numbers (P/N)
Column C has MTBF data for the 5000 line.
Column D has “Actual” or “Estimated”
Column E has max MTBF value from “C” for the P/N in “A” {=MAX(IF(A$2:A
$5050=A3,C$2:C$5050))}
What I am now trying to do unsuccessfully is obtain the “Actual” or
“Estimated” in Column F based on the MAX values returned in Column
“E”. Can someone help?
Also, if the MAX values returned for a given part number has both
“Actual” and “Estimated” the default should be “Actual”.