View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default Max using Index/Match Help

You're welcome!

Biff

"Exceller" wrote in message
...
Thanks, Biff. I appreciate your help!
Exceller

"T. Valko" wrote:

One way:

D2 = 12345

=SUMPRODUCT(MAX((A1:A3=D2)*B1:B3))

Biff

"Exceller" wrote in message
...
I'm trying to create a function using Index/Match to qualify my
criteria
to
extract the highest value in a field (and I'm having fits doing it!). I
can't
use VLookup because my lookup list is, and cannot be, in alphabetical
order.

My fields are as follows:

A B
1 Check number Check Amt
2 12345 $100
3 12345 $200
4 12345 $300

I'm using the check numbers (another sheet, say, at cell D2) as the
Lookup
Value against the Check Number field (above). When a Match is found I
need
the highest value from the Check Amt field returned.

Thank you!