Look up multiple cases return the lowest

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Look up multiple cases return the lowest

Post by YasserKhalil »

Hello everyone
How to lookup "First Name" and "Last Name" of Sheet2 in Sheet1 and return only one color which is the lowest among others: red = the lowest, green = the highest, amber = yellow (between red and green). For example, David Guetta has yellow, green and red, and I need to return only the lowest (in this case red). Aaron Ramsey has yellow and green, but I need yellow which is lower than green. But "First Name" and "Last Name" must be our criteria to lookup and return corresponding but only the lowest one.
You do not have the required permissions to view the files attached to this post.

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Look up multiple cases return the lowest

Post by YasserKhalil »

Any help in this topic.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Look up multiple cases return the lowest

Post by HansV »

I suppose you won't like this:

Code: Select all

Sub MinColor()
    Dim wsh1 As Worksheet
    Dim wsh2 As Worksheet
    Dim m1 As Long
    Dim m2 As Long
    Application.ScreenUpdating = False
    Set wsh1 = Sheet1
    m1 = wsh1.Range("A" & wsh1.Rows.Count).End(xlUp).Row
    wsh1.Range("D2:D" & m1).Formula = _
        "=INDEX({1,2,2,3},MATCH(C2,{""Red"",""Amber"",""Yellow"",""Green""},0))"
    Set wsh2 = Sheet2
    m2 = wsh2.Range("A" & wsh2.Rows.Count).End(xlUp).Row
    With wsh2.Range("C2:C" & m2)
        .Formula = "=INDEX({""Red"",""Yellow"",""Green""}," & _
            "MINIFS(Sheet1!$D$2:$D$8,Sheet1!$A$2:$A$8,A2,Sheet1!$B$2:$B$8,B2))"
        .Value = .Value
    End With
    wsh1.Range("D2:D" & m1).ClearContents
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Look up multiple cases return the lowest

Post by YasserKhalil »

Thanks a lot my tutor. I like any answer from your side. I am big fan of you.
As for the results, the cell C3 in Sheet2 in result (Yellow) while I expect to get Amber as a result of lookup.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Look up multiple cases return the lowest

Post by HansV »

Amber = Yellow, you wrote. If you want to distinguish between them: what if a person has both amber and yellow but not red, which one do you want to return?

The following version will return Amber if both Amber and Yellow occur for the same person, but not red:

Code: Select all

Sub MinColor()
    Dim wsh1 As Worksheet
    Dim wsh2 As Worksheet
    Dim m1 As Long
    Dim m2 As Long
    Application.ScreenUpdating = False
    Set wsh1 = Sheet1
    m1 = wsh1.Range("A" & wsh1.Rows.Count).End(xlUp).Row
    wsh1.Range("D2:D" & m1).Formula = _
        "=INDEX({1,2,3,4},MATCH(C2,{""Red"",""Amber"",""Yellow"",""Green""},0))"
    Set wsh2 = Sheet2
    m2 = wsh2.Range("A" & wsh2.Rows.Count).End(xlUp).Row
    With wsh2.Range("C2:C" & m2)
        .Formula = "=INDEX({""Red"",""Amber"",""Yellow"",""Green""}," & _
            "MINIFS(Sheet1!$D$2:$D$8,Sheet1!$A$2:$A$8,A2,Sheet1!$B$2:$B$8,B2))"
        .Value = .Value
    End With
    wsh1.Range("D2:D" & m1).ClearContents
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Look up multiple cases return the lowest

Post by YasserKhalil »

In case the same person has Amber and Yellow (an as they are both equal) then to lookup the first instance that happened.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Look up multiple cases return the lowest

Post by HansV »

That would slow down the code. I'll leave it to you to do that.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Look up multiple cases return the lowest

Post by YasserKhalil »

I will try to do my best .. I will try to have a deep look.

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Look up multiple cases return the lowest

Post by YasserKhalil »

If we edit the formula in the helper column and add row number Row() like that

Code: Select all

INDEX({1,2,2,4},MATCH(C2,{"Red","Amber","Yellow","Green"},0))& ROW()
How in that case use a formula to rely on the min number?

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Look up multiple cases return the lowest

Post by YasserKhalil »

As you did my tutor in sheet1 in the helper column D this formula is put

Code: Select all

=VALUE(INDEX({1,2,2,4},MATCH(C2,{"Red","Amber","Yellow","Green"},0)) & ROW())
And then the array formula entered in C2

Code: Select all

=INDEX(Sheet1!$C$1:$C$9,MATCH(1,(Sheet1!$A$1:$A$9=A2)*(Sheet1!$B$1:$B$9=B2)*(Sheet1!$D$1:$D$9=MIN(IF(Sheet1!$A$1:$A$9=Sheet2!A2,IF(Sheet1!$B$1:$B$9=B2,Sheet1!$D$1:$D$9)))),0))