Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 55
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 55
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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!






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
INDEX/MATCH Functions with row-arranged table. Bob Keller Excel Worksheet Functions 1 November 2nd 06 01:13 AM
Index/Match in VBA [email protected] Excel Discussion (Misc queries) 3 August 7th 06 04:45 PM
Index/Match not working frosterrj Excel Worksheet Functions 6 May 22nd 06 06:18 PM
Index/Match Help singsunnyboy7 Excel Discussion (Misc queries) 1 July 26th 05 10:18 PM
Index/Match to look up a value in one workbook and insert it into. Jean Marie Excel Discussion (Misc queries) 2 February 22nd 05 02:53 PM


All times are GMT +1. The time now is 11:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"