View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Exceller Exceller is offline
external usenet poster
 
Posts: 55
Default Max using Index/Match Help

My goodness...this is just child's play for you Gurus!
Many, many thanks, Mama--it works like a charm.
Regards.
(aspiring) Exceller

"Teethless mama" wrote:

=MAX(IF(A2:A500="test1",B2:B500))

ctrl+shift+enter, not just enter
Adjust to suit

"Exceller" wrote:

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!