Lookup more than one column?

  • Thread starter Thread starter Bob
  • Start date Start date
B

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.
 
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)))
 
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("B2:D5")
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
 
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("B2:D5")
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
 
Hi.

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

What ?
=IF(SMALL(B2:D5,1)=MIN(B2:D2),INDEX(B1:D1,MATCH(MIN(B2:D2),B2:D2,0)),IF(SMALL(B2:D5,1)=MIN(B3:D3),INDEX(B1:D1,MATCH(MIN(B3:D3),B3:D3
,0)),IF(SMALL(B2:D5,1)=MIN(B4:D4),INDEX(B1:D1,MATCH(MIN(B4:D4),B4:D4,0)),INDEX(B1:D1,MATCH(MIN(B5:D5),B5:D5,0)))))

Alain CROS.
 
Bob

Assuming the data is in the range A1:D5 then the following
array entered will give the appropriate results.

Large Who:
=INDEX(A1:A5,MAX((B2:D5=MAX(B2:D5))*(ROW(B2:D5))))
Large What:
=INDEX(A1:D1,MAX((B2:D5=MAX(B2:D5))*(COLUMN(B2:D5))))
Small Who:
=INDEX(A1:A5,MAX((B2:D5=MIN(B2:D5))*(ROW(B2:D5))))
Small What:
=INDEX(A1:D1,MAX((B2:D5=MIN(B2:D5))*(COLUMN(B2:D5))))

Tony
 
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("B2:D5")
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
 
Back
Top