max and lookup
 

I have 2 columns of data; my item number and amount of quantity reserved. I
want to be able to get the max of a quantity reserved by looking up the item
number. For example, I want to find out the max on Quantity for Item #100 =
10.

Item # Quantity
101 5
100 10
102 4
100 8
102 9
101 11

max and lookup
 

Hi

Try one of these:

=SUMPRODUCT(MAX((A2:A7=100)*(B2:B7)))

OR....

Array entered using the key combo of VTRL,SHIFT,ENTER:

=MAX(IF(A2:A7=100,B2:B7))

Biff

"eoht" wrote in message
...
I have 2 columns of data; my item number and amount of quantity reserved.
I
want to be able to get the max of a quantity reserved by looking up the
item
number. For example, I want to find out the max on Quantity for Item #100
=
10.

Item # Quantity
101 5
100 10
102 4
100 8
102 9
101 11

max and lookup
 

Ooops!

Array entered using the key combo of VTRL,SHIFT,ENTER:

Should be:

Array entered using the key combo of CTRL,SHIFT,ENTER:

Biff

"Biff" wrote in message
...
Hi

Try one of these:

=SUMPRODUCT(MAX((A2:A7=100)*(B2:B7)))

OR....

Array entered using the key combo of VTRL,SHIFT,ENTER:

=MAX(IF(A2:A7=100,B2:B7))

Biff

"eoht" wrote in message
...
I have 2 columns of data; my item number and amount of quantity reserved.
I
want to be able to get the max of a quantity reserved by looking up the
item
number. For example, I want to find out the max on Quantity for Item
#100 =
10.

Item # Quantity
101 5
100 10
102 4
100 8
102 9
101 11

max and lookup
 

This is an array formula, meaning you enter it with Shift+Ctrl+Enter

=MAX(IF(A1:A6=D1,B1:B6,0))

where the Item #s are in A1:A6 and the item# you're interested in is in D1


"eoht" wrote:

I have 2 columns of data; my item number and amount of quantity reserved. I
want to be able to get the max of a quantity reserved by looking up the item
number. For example, I want to find out the max on Quantity for Item #100 =
10.

Item # Quantity
101 5
100 10
102 4
100 8
102 9
101 11

max and lookup
 

If your data in is cells A1:B11, try this:
c1: =SUMPRODUCT(MAX(--(A2:A10=100)*(B2:B10)))

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"eoht" wrote:

I have 2 columns of data; my item number and amount of quantity reserved. I
want to be able to get the max of a quantity reserved by looking up the item
number. For example, I want to find out the max on Quantity for Item #100 =
10.

Item # Quantity
101 5
100 10
102 4
100 8
102 9
101 11