Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max using Index/Match Help
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max using Index/Match Help
=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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max using Index/Match Help
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max using Index/Match Help
You are welcome!
"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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Max using Index/Match Help
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! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX/MATCH Functions with row-arranged table. | Excel Worksheet Functions | |||
Index/Match in VBA | Excel Discussion (Misc queries) | |||
Index/Match not working | Excel Worksheet Functions | |||
Index/Match Help | Excel Discussion (Misc queries) | |||
Index/Match to look up a value in one workbook and insert it into. | Excel Discussion (Misc queries) |