Thread: Match
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Match

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”.