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.
Look up multiple cases return the lowest
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Look up multiple cases return the lowest
You do not have the required permissions to view the files attached to this post.
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Look up multiple cases return the lowest
Any help in this topic.
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Look up multiple cases return the lowest
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
Hans
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Look up multiple cases return the lowest
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.
As for the results, the cell C3 in Sheet2 in result (Yellow) while I expect to get Amber as a result of lookup.
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Look up multiple cases return the lowest
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:
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
Hans
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Look up multiple cases return the lowest
In case the same person has Amber and Yellow (an as they are both equal) then to lookup the first instance that happened.
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Look up multiple cases return the lowest
That would slow down the code. I'll leave it to you to do that.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Look up multiple cases return the lowest
I will try to do my best .. I will try to have a deep look.
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Look up multiple cases return the lowest
If we edit the formula in the helper column and add row number Row() like that
How in that case use a formula to rely on the min number?
Code: Select all
INDEX({1,2,2,4},MATCH(C2,{"Red","Amber","Yellow","Green"},0))& ROW()
-
- PlatinumLounger
- Posts: 4913
- Joined: 31 Aug 2016, 09:02
Re: Look up multiple cases return the lowest
As you did my tutor in sheet1 in the helper column D this formula is put
And then the array formula entered in C2
Code: Select all
=VALUE(INDEX({1,2,2,4},MATCH(C2,{"Red","Amber","Yellow","Green"},0)) & ROW())
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))