Extracting max value

[Prasad]

It seems to be simple but I am stuck with it. Pl go through the attachment. In col F, I want to extract the maximum value of Doc. No. from col. B. What formula should I use?

[HansV]

Enter the following array formula in F2 (confirm with Ctrl+Shift+Enter):

=MAX(IF($A$2:$A$22=E2,$B$2:$B$22))

and fill down.

[HansV]

Alternatively, you can create a pivot table based on the data in A1:B22, with Doc. No in the row area and Base Amt. in the value area. Excel will sum the values by default, but you can double-click Sum of Base Amt. to specify Max as aggregation function.

[Prasad]

Alternatively, you can create a pivot table based on the data in A1:B22, with Doc. No in the row area and Base Amt. in the value area. Excel will sum the values by default, but you can double-click Sum of Base Amt. to specify Max as aggregation function.

Thank you Hans for quick response. Since original WB is a huge one with some other data, pivot table is not feasible. I will go with the array formula provided by you.

Thanks once again