matching and lookup??
 

Job # Item # Quantity
51 100 10
85 105 9
64 103 8
78 103 3
45 100 7
53 101 9
24 101 6

I'm trying to find a function that will let me find the "Job #", by matching
the "Item #" and the max number of "Quantity" used. For example, if I know
that "Item#" = 100 has a max. of 10 quanities used, I want to find a function
that will let me find the "Job #" of that quantity and Item # is 51.

Any Suggestions?

matching and lookup??
 

If your job "number" happens to non-numeric, you might want to try

=INDEX(A2:A8,MATCH(D1&" "&MAX((B2:B8=D1)*(C2:C8)),B2:B8&" "&C2:C8,0))

entered with Shift+Ctrl+Enter, and using same assumptions as previous post

"Duke Carey" wrote:

Assuming your data starts in A2, with the headers in row 1, and the Item# is
in D1, use this array formula - entered with Shift+Ctrl+Enter

=SUMPRODUCT(--(B2:B8=D1),--(C2:C8=MAX(IF(B2:B8=D1,C2:C8,0))),A2:A8)

"eoht" wrote:

Job # Item # Quantity
51 100 10
85 105 9
64 103 8
78 103 3
45 100 7
53 101 9
24 101 6

I'm trying to find a function that will let me find the "Job #", by matching
the "Item #" and the max number of "Quantity" used. For example, if I know
that "Item#" = 100 has a max. of 10 quanities used, I want to find a function
that will let me find the "Job #" of that quantity and Item # is 51.

Any Suggestions?

matching and lookup??
 

Here's the non-array version:

=SUMPRODUCT(--(B2:B8=D1),--(C2:C8=MAX((B2:B8=D1)*(C2:C8))),A2:A8)

Biff

"Duke Carey" wrote in message
...
Assuming your data starts in A2, with the headers in row 1, and the Item#
is
in D1, use this array formula - entered with Shift+Ctrl+Enter

=SUMPRODUCT(--(B2:B8=D1),--(C2:C8=MAX(IF(B2:B8=D1,C2:C8,0))),A2:A8)

"eoht" wrote:

Job # Item # Quantity
51 100 10
85 105 9
64 103 8
78 103 3
45 100 7
53 101 9
24 101 6

I'm trying to find a function that will let me find the "Job #", by
matching
the "Item #" and the max number of "Quantity" used. For example, if I
know
that "Item#" = 100 has a max. of 10 quanities used, I want to find a
function
that will let me find the "Job #" of that quantity and Item # is 51.

Any Suggestions?

matching and lookup??
 

A somewhat shorter, but less obvious, formula than that in my previous
post, which will also handle non-numeric item numbers is:

=INDEX(A2:A8,MATCH(1,--(C2:C8=MAX((B2:B8=D1)*(C2:C8))),0))

Also an array formula - enter with ctl-shift-enter