Lookup more than one column?

[Bob]

Task: Identify the largest or smallest quantity, then
identify who (or what) generated that quantity.

Example:
Restaurant Breakfast Lunch Dinner
Joe's 19 15 64
Sam's 56 51 52
Tim's 40 35 30
Bob's 55 52 63

Problem: The functions LARGE and SMALL perform well but
the next step in identifying who (or what) produced that
quantity is a challenge. [Range names: Restaurant,
Breakfast, Lunch, Dinner]Solution or ideas? Thanks.

 

[Lance]

As an example, to identify Sam's as the largest breakfast
=INDEX(A1:A4,SUMPRODUCT((B1:B4=MAX(B1:B4))*MATCH
(B1:B4,B1:B4,0)))

 

[L. Howard Kittle]

Hi Bob,

Using your example table with "Resturant" in A1 and 63 in D5, this returns
Joe's, Dinner, 64 into J2, K2, L2.

Sub WhoWhatHow()
Dim i As Integer
Dim j As String
Dim k As String
Dim Data As Range

Range("J2,K2,L2").Value = ""

Set Data = Range("B25")
i = Application.WorksheetFunction.Large(Data, 1)

Cells.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

j = ActiveCell.End(xlUp).Value
k = ActiveCell.End(xlToLeft).Value

Range("J2").Value = k
Range("K2").Value = j
Range("L2").Value = i

End Sub

HTH
Regards,
Howard

 

[L. Howard Kittle]

Hi Bob,

Using your example table with "Resturant" in A1 and 63 in D5, this returns
Joe's, Dinner, 64 into J2, K2, L2.

Sub WhoWhatHow()
Dim i As Integer
Dim j As String
Dim k As String
Dim Data As Range

Range("J2,K2,L2").Value = ""

Set Data = Range("B25")
i = Application.WorksheetFunction.Large(Data, 1)

Cells.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

j = ActiveCell.End(xlUp).Value
k = ActiveCell.End(xlToLeft).Value

Range("J2").Value = k
Range("K2").Value = j
Range("L2").Value = i

End Sub

HTH
Regards,
Howard

 

[Alain CROS]

Hi.

Who ?
=IF(SMALL(B25,1)=MIN(B2:B5),INDEX(A2:A5,MATCH(MIN(B2:B5),B2:B5,0)),IF(SMALL(B25,1)=MIN(C2:C5),INDEX(A2:A5,MATCH(MIN(C2:C5),C2:C5
,0)),INDEX(A2:A5,MATCH(MIN(D25),D25,0))))

What ?
=IF(SMALL(B25,1)=MIN(B22),INDEX(B11,MATCH(MIN(B22),B22,0)),IF(SMALL(B25,1)=MIN(B33),INDEX(B11,MATCH(MIN(B33),B33
,0)),IF(SMALL(B25,1)=MIN(B44),INDEX(B11,MATCH(MIN(B44),B44,0)),INDEX(B11,MATCH(MIN(B55),B55,0)))))

Alain CROS.

 

[acw]

Bob

Assuming the data is in the range A15 then the following
array entered will give the appropriate results.

Large Who:
=INDEX(A1:A5,MAX((B25=MAX(B25))*(ROW(B25))))
Large What:
=INDEX(A11,MAX((B25=MAX(B25))*(COLUMN(B25))))
Small Who:
=INDEX(A1:A5,MAX((B25=MIN(B25))*(ROW(B25))))
Small What:
=INDEX(A11,MAX((B25=MIN(B25))*(COLUMN(B25))))

Tony

 

[L. Howard Kittle]

Hi Bob,

Using your example table with "Resturant" in A1 and 63 in D5, this returns
Joe's, Dinner, 64 into J2, K2, L2.

Sub WhoWhatHow()
Dim i As Integer
Dim j As String
Dim k As String
Dim Data As Range

Range("J2,K2,L2").Value = ""

Set Data = Range("B25")
i = Application.WorksheetFunction.Large(Data, 1)

Cells.Find(What:=i, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

j = ActiveCell.End(xlUp).Value
k = ActiveCell.End(xlToLeft).Value

Range("J2").Value = k
Range("K2").Value = j
Range("L2").Value = i

End Sub

HTH
Regards,
Howard