Comparing rows of data

[matlocktm]

Hello all,

I need to be able to compare rows of data to effect an end result (see
reference table below). If the item numbers match in B, I need to
check the values in the corresponding cells of column C - and if the
value in any of those cells equal zero, I need to check the values of
the corresponding cells in column D, and take the highest value within
that range and return that number in the corresponding cells in column
E. Is this possible? And if so, is there a quick and dirty way to
accomplish this? Any help is greatly appreciated.


A B C D E

ST Item ROP LdTime Result

1 WI 1002 7 42

2 IA 1002 0 31

3 WI 1003 0 16

4 IA 1003 60 16

5 WI 1004 1 31

6 IA 1004 4 31

 

[Frank Kabel]

Hi
withh what data do you want to match column B?. Do you have a second
list with all items of interest

 

[Myrna Larson]

This is my interpretation of your problem. We'll use row 2 as an example. If
C2<>0, E2 = the value in C2.

If C2=0, E2 = the maximum of the values in column C for those rows where the
value in column B equals B2.

I believe the following array formula will do what you want. It must be
entered with CTRL+SHIFT+ENTER.

=IF(C2<>0,C2,MAX(IF($B$2:$B$100=B2,$C$2:$C$100)))