S
Shaun
How would I add logic to this formula =VLOOKUP($A2,EXP,6,FALSE) so that it
returns the largest value if I have multiple matches in the table?
returns the largest value if I have multiple matches in the table?
J
Jim Thomlinson
Vlookup does not deal well with multiple matches. Does your existing formula
return a number or Text. If it is a number we can look into using sumproduct.
If not things get difficult.
return a number or Text. If it is a number we can look into using sumproduct.
If not things get difficult.
S
Shaun
well it is a date so in a way yes it is a number.
J
JLatham
I agree with Jim Thomlinson; things could get pretty icky. If there was a
small limit to the number of potential duplicate entries, you might code up a
solution, but if the potential is beyond a few (guessing at 5 or 6 as a
practical limit), then the formula would probably be too long/complex to deal
with unless we go with some slick variation of SUMPRODUCT(). And I hadn't
even thought about that one yet.
What I have come up with is a hybrid solution, combining both Excel
worksheet functions COUNTIF() and VLOOKUP() along with a user defined
function (UDF) to deal with the times there are multiple matches.
Rules for my example: Your EXP table occupies range $E$2 over & down to
$J$7. Obviously it's probably larger than that, but keep my limit in mind
when looking at the example formula:
=IF(COUNTIF($E$2:$E$7,$A$2)=1,VLOOKUP($A2,EXP,6,FALSE),
getlargestofmultiples($A$2,$E$2:$J$7,6))
Remember that would be all one line in an Excel cell formula.
What that says is: if there's only one match to whatever is in A2 in the
first column of the lookup table, EXP ($E$2:$J$7, remember) then stick with
the fast VLOOKUP() formula, but if the count is either 0 or greater than 1,
then use the UDF. Actually we could use a second COUNTIF() to keep from
running the UDF uselessly when COUNTIF() returned zero, but we'll write that
later. Now, here is the code for the UDF. To put it into use, press
[ALT]+[F11] to open the VB editor, then use Insert --> Module to open a new
code module, and copy the code below into the module and then close the VB
editor.
Function GetLargestOfMultiples(seekValue As Range, _
searchList As Range, returnColumn As Integer) As Variant
'Returns largest value associated with multiple entries
'in a table. Is not case sensitive.
'parameters are similar to, but NOT exactly like those
'of a VLOOKUP() formula
' INPUT PARAMETERS:
' seekValue = address of value to match in the table
' searchList = address of single column of entries to
' find matches in. Much as 1st column of
' a VLOOKUP table
' returnColumn = integer of column relative to the
' searchList, INCLUDING the searchList
' column, so it's just like the
' return column value in a VLOOKUP()
'
'
Dim anyEntry As Range ' to look through searchList
' better to type foundValue as same as
' you anticipate returning such as
' integer, long, currency, etc if able
' Does currently assume some numeric value to be returned
Dim foundValue As Variant
'set up a default/failed return value
GetLargestOfMultiples = "No Match Found"
foundValue = 9E-99 ' make it a very small number
'make it foundValue="" if you are returning text values
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = UCase(Trim(seekValue)) Then
'we found a match
If anyEntry.Offset(0, returnColumn - 1) _
End If
End If
Next
If foundValue <> 9E-99 Then ' compare against original
GetLargestOfMultiples = foundValue
End If
End Function
That's it. Since our original formula would cause this routine to be run
when the value in A2 doesn't even exist in the list, we might want to skip
running a potentially long process (the UDF) when there's no hope of
returning a value, so
we could modify the original formula to this
=IF(COUNTIF($E$2:$E$7,$A$2)=1,VLOOKUP($A$2,EXP,6,FALSE),IF(COUNTIF($E$2:$E$2,$A2)>0,getlargestofmultiples($A$2,$E$2:$J$7,6),"not in list"))
small limit to the number of potential duplicate entries, you might code up a
solution, but if the potential is beyond a few (guessing at 5 or 6 as a
practical limit), then the formula would probably be too long/complex to deal
with unless we go with some slick variation of SUMPRODUCT(). And I hadn't
even thought about that one yet.
What I have come up with is a hybrid solution, combining both Excel
worksheet functions COUNTIF() and VLOOKUP() along with a user defined
function (UDF) to deal with the times there are multiple matches.
Rules for my example: Your EXP table occupies range $E$2 over & down to
$J$7. Obviously it's probably larger than that, but keep my limit in mind
when looking at the example formula:
=IF(COUNTIF($E$2:$E$7,$A$2)=1,VLOOKUP($A2,EXP,6,FALSE),
getlargestofmultiples($A$2,$E$2:$J$7,6))
Remember that would be all one line in an Excel cell formula.
What that says is: if there's only one match to whatever is in A2 in the
first column of the lookup table, EXP ($E$2:$J$7, remember) then stick with
the fast VLOOKUP() formula, but if the count is either 0 or greater than 1,
then use the UDF. Actually we could use a second COUNTIF() to keep from
running the UDF uselessly when COUNTIF() returned zero, but we'll write that
later. Now, here is the code for the UDF. To put it into use, press
[ALT]+[F11] to open the VB editor, then use Insert --> Module to open a new
code module, and copy the code below into the module and then close the VB
editor.
Function GetLargestOfMultiples(seekValue As Range, _
searchList As Range, returnColumn As Integer) As Variant
'Returns largest value associated with multiple entries
'in a table. Is not case sensitive.
'parameters are similar to, but NOT exactly like those
'of a VLOOKUP() formula
' INPUT PARAMETERS:
' seekValue = address of value to match in the table
' searchList = address of single column of entries to
' find matches in. Much as 1st column of
' a VLOOKUP table
' returnColumn = integer of column relative to the
' searchList, INCLUDING the searchList
' column, so it's just like the
' return column value in a VLOOKUP()
'
'
Dim anyEntry As Range ' to look through searchList
' better to type foundValue as same as
' you anticipate returning such as
' integer, long, currency, etc if able
' Does currently assume some numeric value to be returned
Dim foundValue As Variant
'set up a default/failed return value
GetLargestOfMultiples = "No Match Found"
foundValue = 9E-99 ' make it a very small number
'make it foundValue="" if you are returning text values
For Each anyEntry In searchList
If UCase(Trim(anyEntry)) = UCase(Trim(seekValue)) Then
'we found a match
If anyEntry.Offset(0, returnColumn - 1) _
foundValue = anyEntry.Offset(0, returnColumn - 1)
End If
End If
Next
If foundValue <> 9E-99 Then ' compare against original
GetLargestOfMultiples = foundValue
End If
End Function
That's it. Since our original formula would cause this routine to be run
when the value in A2 doesn't even exist in the list, we might want to skip
running a potentially long process (the UDF) when there's no hope of
returning a value, so
we could modify the original formula to this
=IF(COUNTIF($E$2:$E$7,$A$2)=1,VLOOKUP($A$2,EXP,6,FALSE),IF(COUNTIF($E$2:$E$2,$A2)>0,getlargestofmultiples($A$2,$E$2:$J$7,6),"not in list"))
C
Chip Pearson
It isn't clear what you are asking for. By largest value with multiple
matches, do you mean to return the item that is in the highest
numbered row? Or do you mean the largest value from the lookup? For
example, suppose you have in C4:C20 and D4
20,
1 100
2 200
3 900
1 400
2 500
3 600
4 700
If you are looking up the value 3 in C4:C20, which instance of 3 do
you want to return? The first lookup match for 3 is 900 and the last
row match for 3 is 600. Which you do want? To get the value in D for
the last occurrence of 3 in C, use the following array formula:
=OFFSET(C$4,MAX((C4:C20=3)*(ROW(C4:C20)))-ROW(C$4),1,1,1)
To get the max value in D where C = 3, use
=MAX((C4:C20=3)*(D420))
This is an array formula, so you must press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel will display
the formula enclosed in curly braces { }. You do not type
in the braces -- Excel puts them in automatically. The
formula will not work properly if you do not enter it with
CTRL SHIFT ENTER. For much more information about array
formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx.
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
matches, do you mean to return the item that is in the highest
numbered row? Or do you mean the largest value from the lookup? For
example, suppose you have in C4:C20 and D4
20,1 100
2 200
3 900
1 400
2 500
3 600
4 700
If you are looking up the value 3 in C4:C20, which instance of 3 do
you want to return? The first lookup match for 3 is 900 and the last
row match for 3 is 600. Which you do want? To get the value in D for
the last occurrence of 3 in C, use the following array formula:
=OFFSET(C$4,MAX((C4:C20=3)*(ROW(C4:C20)))-ROW(C$4),1,1,1)
To get the max value in D where C = 3, use
=MAX((C4:C20=3)*(D4
20))This is an array formula, so you must press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel will display
the formula enclosed in curly braces { }. You do not type
in the braces -- Excel puts them in automatically. The
formula will not work properly if you do not enter it with
CTRL SHIFT ENTER. For much more information about array
formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx.
Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
M
Max
Using your defined name EXP, this seems to work ok
Array-enter, ie press CTRL+SHIFT+ENTER to confirm:
=MAX(IF(OFFSET(EXP,,,,1)=$A2,OFFSET(EXP,,5,,1)))
The "5" in the offset's col param is equivalent to the "6" (the col index)
in your vlookup. Success? celebrate it, hit the YES below
Array-enter, ie press CTRL+SHIFT+ENTER to confirm:
=MAX(IF(OFFSET(EXP,,,,1)=$A2,OFFSET(EXP,,5,,1)))
The "5" in the offset's col param is equivalent to the "6" (the col index)
in your vlookup. Success? celebrate it, hit the YES below
S
Shaun
Max thanks for the poke at this but I need it to keep the reference in mind
when looking for the highest values of that reference.
For example
EXP table:
A F
1 Bob 150
2 Jed 90
3 Tom 80
4 Bob 100
5 Jed 180
6 Tom 160
Desired look up: (so that I am only looking at the highest value of
employee’s payout.)
A F
1 Bob 160
2 Jed 180
3 Tom 160
Any help would be great!
when looking for the highest values of that reference.
For example
EXP table:
A F
1 Bob 150
2 Jed 90
3 Tom 80
4 Bob 100
5 Jed 180
6 Tom 160
Desired look up: (so that I am only looking at the highest value of
employee’s payout.)
A F
1 Bob 160
2 Jed 180
3 Tom 160
Any help would be great!
M
Max
But Shaun, as-is, that is exactly what the earlier expression evaluates and
returns as the result. It should have worked ok for you. I tested it fine
here before posting. Can you re-check that you have array-entered (CSE) the
expression correctly?Visually look for the curly braces { } in the formula
bar of the cell where you placed that earlier expression. These curlies are
inserted by Excel if the CSE** is correctly done. If you see the curlies, its
fine. If not, then it means that the formula was NOT array-entered, and hence
it will not evaluate correctly. I know of no other way to check that a
formula has been correctly array-entered.
**CSE = pressing CTRL+SHIFT+ENTER to confirm the formula (instead of just
pressing ENTER)
Try it again. Then do it justice, re-rate the earlier response.
returns as the result. It should have worked ok for you. I tested it fine
here before posting. Can you re-check that you have array-entered (CSE) the
expression correctly?Visually look for the curly braces { } in the formula
bar of the cell where you placed that earlier expression. These curlies are
inserted by Excel if the CSE** is correctly done. If you see the curlies, its
fine. If not, then it means that the formula was NOT array-entered, and hence
it will not evaluate correctly. I know of no other way to check that a
formula has been correctly array-entered.
**CSE = pressing CTRL+SHIFT+ENTER to confirm the formula (instead of just
pressing ENTER)
Try it again. Then do it justice, re-rate the earlier response.