matching and lookup??

E

eoht

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?
 
D

DOR

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
 
B

Biff

Here's the non-array version:

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

Biff
 
D

DOR

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
 
Top